LiveCode Forums

Mark wrote:I guess we need to test this some time. I neer use LiveCode's built-in SQL commands, but when I have some time I'll try putting SQL syntax into the variables.

I am currently doing the 'Business Academy' offered by RunRev, and the latest daily lesson is about performing SQL queries. During the lesson, it is shown that:

1) if you build your entire SQL query yourself, then you have to escape your own characters and do your own sanitisation

2) LiveCode *can* do this automatically for you, if you place your query terms into variables first, and then place your variables into the query using placeholders, as explained in the dictionary. Using this method, LiveCode will escape and encode the data appropriately, supposedly even taking into consideration the database type and connection settings.

So, if you are building your query like this, LiveCode is not helping, and you do need to escape your own query:
However, if you are doing your query like THIS, then LiveCode is already building a reasonably secure query for you:
I hope this helps others, too!

Yes, Livecode internally calls the appropriate function native to the SQL database implementation to do all the necessary escaping when you use the parameter substitution form of revDataFromQuery.
Pete

Can someone explain to me in lamen's terms what the difference is between the two different sql statements? As in what is actually going on in the background when you use the :1 in lieu of the &variable. I've been trying to find something on this for a few weeks but I haven't really understood what's going on. I understand how SQL Injection works, but I don't seem to fully grasp yet what the code is actually doing to prevent it. Is it something built into SQLITE or MySQL that is doing something or is it Livecode? I don't seem to understand it. Thus, I'm having a hard time understanding how to use the parameterized query.
So is it the colon character then something in the sql statement or the colon one ":1" then something and even then what is actually happening when this is coded this way? Sorry if these are stupid questions.