13 Replies Latest reply on Aug 23, 2012 4:26 AM by Mike_Mitchell

    ExecuteSQL and "?"

    Mike_Mitchell

      Okay, I'm definitely not a SQL whiz, and I do not get the use of the "?" in the SQL statements. Can somebody explain this to me? For example:

       

      SELECT * FROM table WHERE table.DateField <= ?

       

      I can't find this anywhere except as a single character wildcard. How does that work when you're querying a date field?

       

      Or what about this one?

       

      ExecuteSQL (

      "SELECT * FROM table WHERE table.field IN (?,?,?)" ; " " ; "|" ; "(A,B,C)" ) (I think I got that syntax right)

       

      What the heck is that?

       

      'Splain to the dummy, please.

       

      Mike

        • 1. Re: ExecuteSQL and "?"
          nsabel

          the ? is used to add arguments to the SQL statment. For example you want to use a field for your WHERE criteria using arguments would look like this

           

          ExecuteSQL ( "SELECT * FROM table WHERE field = ?" ; "" ; "" ; fmTable:fmField )

           

          If you did not use the argument it would have to be written like this.

           

          ExecuteSQL ( "SELECT * FROM table WHERE field = " & fmTable:fmField ; "" ; "" )

           

          Arguments are used in the order they occur in the SELECT statement and are seperated by a ;

           

          The seconds statement will not work because each ? needs to be declared seperatly.

           

          ExecuteSQL ( "SELECT * FROM table WHERE table.field IN (?,?,?)" ; " " ; "|" ; "A" ; "B" ; "C" )

           

          Another reason for the use of the arguments is when you have a single quote in your statement. for example

           

          ExecuteSQL ( "SELECT * FROM table WHERE field = 'can''t' ; "" ; "" ) note: a second single quote is required to make this work

           

          with arguments

           

          ExecuteSQL ( "SELECT * FROM table WHERE field = ? " ; "" ; "" ; "can't" )

           

          Hope this helps.

           

          Nick

          • 2. Re: ExecuteSQL and "?"
            Mike_Mitchell

            Ah, very good! So it's specific to ExecuteSQL. No wonder I (along with some of my SQL jocks here) were scratching our heads.

             

            Thank you sir!

             

            Mike

            • 3. Re: ExecuteSQL and "?"
              gdurniak

              It depends on the vendor. It appears Access and Sybase also accept ? as a parameter

               

              greg

               

              > So it's specific to ExecuteSQL

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL and "?"
                Mike_Mitchell

                Thanks, Greg. No wonder when people say, "Do you know SQL?", the first question ought to be, "Which one?"

                 

                 

                • 5. Re: ExecuteSQL and "?"
                  DavidJondreau

                  While dynamic parameters can be useful for writing the actual SQL statements, they do have an entirely separate primary purpose, which is to prevent injection attacks.

                  • 6. Re: ExecuteSQL and "?"
                    DavidJondreau

                    To elaborate:

                     

                    Say you have a database with a CLIENTS table with a ClientName field. There are many client records. You also have a RESULT table storing data including ClientName and a confidential Score field. Each client has a dozen scores. Users log on to see their scores. They can also change their name. You use ExecuteSQL to grab their scores matching on their name: ExecuteSQL ( "SELECT ClientName,Score FROM RESULTS WHERE ClientName='" &  CLIENTS::ClientName & "' ; "" ; "" )

                     

                    WhenTom logs in, goes to his record, and runs his scores, ExecuteSQL() calculates the request as: "SELECT ClientName,Score FROM RESULTS WHERE ClientName='Tom'"

                     

                    However, if "Tom" changes his name to "Tom' OR ClientName='%" then you're running an ExecuteSQL() on "SELECT ClientName,Score FROM RESULTS WHERE ClientName='Tom' OR ClientName='%'" and Tom will see *every* score.

                     

                    By using ExecuteSQL ( "SELECT ClientName,Score FROM RESULTS WHERE ClientName=?" ; "" ; "" ; CLIENTS::ClientName ) you avoid that second possibility. I don't know what's happening in the background.

                     

                    I'm sure there's much more a smart SQL hacker can do than this example, but that's the idea...

                     

                    EDIT: Tom doesn't even need to know the name of the ClientName field, changing his name to "xyz' OR '1'='1" will work just fine.

                     

                    Message was edited by: DavidJondreau

                    1 of 1 people found this helpful
                    • 7. Re: ExecuteSQL and "?"
                      Mike_Mitchell

                      Thanks, David. Good info.

                      • 8. Re: ExecuteSQL and "?"
                        nsabel

                        David brings up a VERY IMPORTANT point I didn't mention as it is the only real use for arguments that Filemaker has in the documentation . Use of arguments primary function is to prevent security vulnerabilities through injection attacks.

                        • 9. Re: ExecuteSQL and "?"
                          pennyruddell

                          Just to mention that http://www.filemakerhacks.com/ has some excellent ExecuteSQL articles, including information on Dynamic Parameters.

                          Penny

                          • 10. Re: ExecuteSQL and "?"
                            Mike_Mitchell

                            On reflection, I'm a little confused by this. In your example, "Clients::ClientName" evaluates to its current contents ... which would be "Tom" or the injection string. So how does moving "Clients::ClientName" to a parameter prevent Tom from injecting SQL code? Does FileMaker automatically strip SQL commands out of the parameter?

                             

                            Just for my own edification, mind you ...   

                             

                            Mike

                            • 11. Re: ExecuteSQL and "?"
                              nsabel

                              When you build the ExecuteSQL statement using the ExecuteSQL ( "SELECT * FROM table WHERE field = " & fmTable:fmField & "'" ; "" ; "" ) method you have add in all of the required single quotes. If you notice in Davids example "Tom' OR ClientName='%" Tom has no opening single quote and the % wildcard has no closing single quote. What happens is this changes the SQL statement to ExecuteSQL ( "SELECT * FROM table WHERE field = 'Tom' OR ClientName='%' " ; "" ; "" ) hence the injection. When it is used as an argument the whole argument text is used in the WHREE field = "argument". I'm not 100% on the inner workings of Filemaker in accomplishing this task, but it falls inline with my eairlier example of the single quote being used in a statement vs brought in with an argument.

                               

                              David might be able to better explain what Filemaker is actually doing under the hood to accomplish this.

                               

                              Nick

                              • 12. Re: ExecuteSQL and "?"
                                tech_liaison

                                I'd also like to mention that the FileMaker Help menu is also quite handy. 

                                • 13. Re: ExecuteSQL and "?"
                                  Mike_Mitchell

                                  When in doubt, RTFM. (Read The Friendly Manual)    

                                   

                                  Thanks, Dave.