4 Replies Latest reply on May 19, 2008 6:51 AM by bre358

    SQL Syntax issue

    bre358 Level 1
      While working on the login functionality of the application I'm developing, I noticed that SQL seems to be interpereted differently than what I'm used to. Here's my current select statement that I'm using for validation:

      sqlText = "SELECT * FROM Up18Technicians WHERE TechName = " + userName + " AND Password = " + pass;

      Which translates to:

      SELECT * FROM Up18Technicians WHERE TechName = Ross AND Password = 358029

      Now, when the application gets to the execute portion, it throws this error:

      SQLError: 'Error #3115: SQL Error.', details:'no such column: Ross', operation:'execute'
      at flash.data::SQLStatement/internalExecute()
      at flash.data::SQLStatement/execute()
      at MatMgr::DataManager/techLogin()[C:\Tech_Connect\MatMgr_Proto\src\MatMgr\DataManager.as:45 3]
      at MatMgr_Proto/login()[C:\Tech_Connect\MatMgr_Proto\src\MatMgrProtoSource.as:210]
      at MatMgr_Proto/__btnLogin_click()[C:\Tech_Connect\MatMgr_Proto\src\MatMgr_Proto.mxml:18]

      This indicates that it is trying to find columns by the name of what was entered instead of trying to find values in those columns matching what I entered. I looked up SQL as its understood by SQL lite, but it seems a bit confusing and I'm unsure if it's even the problem. If it is, then I'm just wondering if it may have something to with perhaps the "AND" keyword isn't understood or maybe there's something up with how I'm using the WHERE clause.

      Here's the function I'm using to perform this in case it helps:

      public function techLogin(userName:String, pass:String):void
      { //This loads information needed to login technicians
      sqlText = "SELECT * FROM Up18Technicians WHERE TechName = " + userName + " AND Password = " + pass;
      trace(sqlText);
      selectStatement = new SQLStatement();
      conn = new SQLConnection();
      selectStatement.sqlConnection = conn;
      conn.open(dbFile);
      selectStatement.addEventListener(SQLEvent.RESULT, selectTechLoginResult);
      selectStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
      selectStatement.text = sqlText;
      selectStatement.execute(); //Error happens here, doesn't go any further
      }
        • 1. Re: SQL Syntax issue
          Alexlives
          Try putting single quotes around the search. It maybe giving you a error that is really for a column type that the SQL is not recognizing.

          Your Example: SELECT * FROM Up18Technicians WHERE TechName = Ross AND Password = 358029

          try

          SELECT * FROM Up18Technicians WHERE TechName = 'Ross' AND Password = '358029'

          Let me know if this works.
          • 2. Re: SQL Syntax issue
            bre358 Level 1
            It worked, thanks I tried putting my entries inside single quotes when I typed it into the login screen I made and the error wasn't thrown. However now I just need to figure out how to place the single quotes around then within the code instead of asking, "Please place single quotes around your user name and password". The code looks like this:

            sqlText = "SELECT * FROM Up18Technicians WHERE TechName = " + userName + " AND Password = " + pass;

            Where it simply takes the text values, I just need to figure out how to get the single quotes in there around the values at runtime automatically.

            Thanks for the help.
            • 3. Re: SQL Syntax issue
              Dr. Fred Mbogo
              sqlText = "SELECT * FROM Up18Technicians WHERE TechName = '" + userName + "' AND Password = '" + pass + "'";

              The only tricky bit is handing inputs with single quotes in them. They need to be turned into doubled single quotes, which SQL interprets as an escaped single quote.
              • 4. Re: SQL Syntax issue
                bre358 Level 1
                Well I figured all that out, but after being reminded about SQLInjection attacks and such, I went with something like this:

                sqlText = "SELECT TechName FROM Up18Technicians WHERE TechName = :userName AND Password = :pass";

                Simply using parameters instead of text that becomes part of the SQL statement.