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
ExecuteSQL ( "SELECT * FROM table WHERE field = ? " ; "" ; "" ; "can't" )
Hope this helps.
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!
1 of 1 people found this helpful
It depends on the vendor. It appears Access and Sybase also accept ? as a parameter
> So it's specific to ExecuteSQL
Thanks, Greg. No wonder when people say, "Do you know SQL?", the first question ought to be, "Which one?"
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.
1 of 1 people found this helpful
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
Thanks, David. Good info.
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.
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 ...
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.
When in doubt, RTFM. (Read The Friendly Manual)