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

    ExecuteSQL and "?"


      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.



        • 1. Re: ExecuteSQL and "?"

          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.



          • 2. Re: ExecuteSQL and "?"

            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!



            • 3. Re: ExecuteSQL and "?"

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




              > So it's specific to ExecuteSQL

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

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



                • 5. Re: ExecuteSQL and "?"

                  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 "?"

                    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 "?"

                      Thanks, David. Good info.

                      • 8. Re: ExecuteSQL and "?"

                        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 "?"

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


                          • 10. Re: ExecuteSQL and "?"

                            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 ...   



                            • 11. Re: ExecuteSQL and "?"

                              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.



                              • 12. Re: ExecuteSQL and "?"

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

                                • 13. Re: ExecuteSQL and "?"

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


                                  Thanks, Dave.