How To Protect Against SQL Injection Attack?

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

igorBUSvRhc
Posts: 37
Joined: Thu Apr 19, 2012 8:12 am

Re: How To Protect Against SQL Injection Attack?

Post by igorBUSvRhc » Sun May 27, 2012 2:41 am

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:

Code: Select all

get revDataFromQuery(tab, return, gConnectionID, "SELECT * FROM users WHERE email LIKE '%" & theSearchString & "%'")
However, if you are doing your query like THIS, then LiveCode is already building a reasonably secure query for you:

Code: Select all

get revDataFromQuery(tab, return, gConnectionID, "SELECT * FROM users WHERE email LIKE :1", "theSearchString")
I hope this helps others, too!

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: How To Protect Against SQL Injection Attack?

Post by phaworth » Sun May 27, 2012 5:33 am

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

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: How To Protect Against SQL Injection Attack?

Post by Not a lot of thought » Fri Dec 04, 2015 3:37 am

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

Post Reply

Return to “Databases”