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

How To Protect Against SQL Injection Attack?

Post by igorBUSvRhc » Tue May 08, 2012 6:55 am

Hi all,

I am writing an app that will process user data in and out of a database. The user data may be tainted or malicious. As far as I can tell, the LiveCode database commands like 'revQueryDatabase' and 'revExecuteSQL' do not automatically sanitise data that is passed to them via placeholder variables.

Is there a way to sanitise user input against sql injection attacks in LiveCode?

Any guidance much appreciated.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Tue May 08, 2012 10:03 am

Hi,

If you're using commands like revExecuteSQL, it means that your web host allows for external access to the MySQL database server. Normally, web hosts only allows access from localhost or from their own IP numbers and the only way to access the server is through PHP or a CGI script. If external access is allowed, a hacker only needs to find out your user name (often the first 8 characters of your main domain name) and start a brute force attack guessing your password. There is no way to be protected against this, except for disabling external access.

If you have safety concerns, I offer web hosting services and can help you to set up a safe(r) connection between livecode, PHP and MySQL.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: How To Protect Against SQL Injection Attack?

Post by bangkok » Tue May 08, 2012 10:14 am

igorBUSvRhc wrote: Is there a way to sanitise user input against sql injection attacks in LiveCode?.
Of course.

Sanitize = removing strings that could be dangerous, because interpretated by SQL : like

+
=
,
single quote
;
space

etc.

So, i use a really simple function :

Code: Select all

function sanitize str

--to avoid very long str
put char 1 to 20 of str into str

replace quote with "" in str
replace "'" with "" in str
replace space with "" in str
replace "," with "" in str
replace ";" with "" in str
replace "=" with "" in str

return str
end sanitize

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Tue May 08, 2012 10:18 am

Hi Bangkok,

That makes no sense if a direct connection to the MySQL server is possible. It only makes sense if you're using PHP or a similar language.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: How To Protect Against SQL Injection Attack?

Post by bangkok » Tue May 08, 2012 2:50 pm

Mark wrote: That makes no sense if a direct connection to the MySQL server is possible. It only makes sense if you're using PHP or a similar language.
You're right... but Igor didn't speak specifically about the issue of local or remote connexions on the SQL server : just the sanitization process.
:)

I forgot to add : i use the sanitization process on a server with LiveCode Server an MySQL server, of course.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Tue May 08, 2012 2:56 pm

Hi,

Yes, with LiveCode server it is good habit to do this.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

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

Re: How To Protect Against SQL Injection Attack?

Post by igorBUSvRhc » Wed May 09, 2012 1:23 am

@Mark, thank you for the input:
Mark wrote:If you're using commands like revExecuteSQL, it means that your web host allows for external access to the MySQL database server.
I am actually developing a desktop app that is going to access data stored in a MySQL database running in the On-Rev servers. So I'll be using LiveCode both on the client, as well as on the server. Commands like 'revExecuteSQL' will be executed on the server, after it receives an http request from the desktop client. The database, therefore, is not open for external access, but I will be accessing it using LiveCode. I guess if someone is using LiveCode Server by itself or as an Apache cgi, the same setup would apply.

I am concerned about security, because I have noticed that commands like 'revExecuteSQL' already do *some* escaping - i.e., I've noticed that it puts single-quotes around string parameter data - but I do not know whether it properly prevents sql injection attacks, or whether this is something we have to implement from scratch.

@bangkok - thank you for showing me your 'sanitising' function. Unfortunately, removing unwanted chars is not always a workable approach - for instance:
* 'name' fields are always problematic, because you *have* to allow for characters like dashes, apostrophes and single-quotes ("O'Brien", "Lee-Wen", etc.)
* 'text' fields are even more problematic, because users will inevitably want to type semi-colons

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Wed May 09, 2012 1:33 am

Hi,

The safest way is to hardcode your queries. Rather than sending complete queries from the desktop app to the server, only send a code and some data to the server. The code refers to a particular query and the data completes the query before it is executed. If no or an incorrect code is sent, you can ignore the request.

If you additionally encrypt the codes and the data sent to the server and decrypt them on the server, then you have quite a safe system. Whenever the server tries to decrypt a command but is unable to do so, it can ignore the query. If it is able to decrypt but it can't detect the correct code, still ignore the query. Only if the request can be decrypted and the code is found, then execute the query.

Instead of hardcoding, you can also send complete MySQL queries to the server and have them executed there. If you encrypt the queries and decrypt them on the server, then you still have a relatively safe system.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

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

Re: How To Protect Against SQL Injection Attack?

Post by igorBUSvRhc » Wed May 09, 2012 2:06 am

Mark, thank you very much for the - very quick - response:
Mark wrote:The safest way is to hardcode your queries. Rather than sending complete queries from the desktop app to the server, only send a code and some data to the server. The code refers to a particular query and the data completes the query before it is executed. If no or an incorrect code is sent, you can ignore the request.

If you additionally encrypt the codes and the data sent to the server and decrypt them on the server, then you have quite a safe system.
This is actually what I'm doing at the moment, but it is still not secure. I'll explain. Consider the 'login' screen for the system:

1) client types in their email address and password
2) Email is encoded with an rsa public key and then base64encoded. Password is sha1-hashed, then rsa-encrypted, then base64-encoded. Both are then transmitted to the server.
3) server then decodes and decrypts everything, and then runs the (hard-coded) username+password query on the database.

This alone, unfortunately, does not protect against SQL injection. In the case of the email field, for instance, a malicious hacker could type in SQL code instead of their email address. The code would be encrypted and encoded by the client app, then decoded and decrypted by the server, who would then insert it into the hard-coded query, and possibly cause serious damage to the database. The server *must* sanitise or check the data in some way before submitting the query to the database.

In this specific case, checking the data is not difficult, because we know that we need an email address, and we can therefore just check for the (normally) allowed characters of "a-z", "A-Z", "0-9" and "@._-". So, once the server decodes and decrypts what it *thinks* is an email, it then checks to make sure that *only* those characters are in the string. If there are other characters present, then it returns an error to the client.

This, however, is not an approach that can be used in every situation. In this app, for instance, users will need to be able to type "reports" - ie., long lengths of text - which will inevitably *have* to include everything: semi-colons, apostrophes, equal signs, etc.

Some frameworks have features to automatically prevent against SQL injection - like automatically 'escaping' or encoding suspicious characters, confirming the data type in query parameters, or even simply preventing that more than a single SQL statement is sent within a single query. I was wondering whether the LiveCode query functions already did any of that, but it doesn't appear so.

I guess I might have to roll up my sleeves and get down to coding my own SQL-safety functions, then!

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: How To Protect Against SQL Injection Attack?

Post by bangkok » Wed May 09, 2012 9:46 am

igorBUSvRhc wrote: I guess I might have to roll up my sleeves and get down to coding my own SQL-safety functions, then!
You summarized the issue : you need to "tailor" everything.

I believe the best system is a multiple system :

-the desktop app would send only "parameters"
-a mixing of "hard coded" queries on the server (UPDATE mytable set XX=parameter1 WHERE BB=parameter2, therefore if the hacker sends a subquery nested in the parameter, it won't work, it will give only an error)
-a sanitazition system, adapted to each type of query and/or type of parameters

Regarding your encryption system... sounds nice but how do know that the user who identifies himself with login+password on the desktop app, will be the same who will send later a SQL query to the server ?

Personaly, I use a system of "cookie".

-when the user identifies himself (successfuly) the server sends back à "cookie" (encrypted)

-this "cookie" is stored on the DB, along with login, and timestamp (it makes a good "logs" also)

-after when the user sends a SQL query, the desktop app sends also the "cookie" and the login, along with the parameters of the SQL query

-then the server will check if the couple login/cookie exists in the logs table, with a time constraint (current time = same day for instance, or not more than X hours after the insert into the logs table)

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

Re: How To Protect Against SQL Injection Attack?

Post by igorBUSvRhc » Mon May 14, 2012 10:44 am

@bangkok, thank you for the suggestions:
bangkok wrote:I believe the best system is a multiple system :

-the desktop app would send only "parameters"
-a mixing of "hard coded" queries on the server (UPDATE mytable set XX=parameter1 WHERE BB=parameter2, therefore if the hacker sends a subquery nested in the parameter, it won't work, it will give only an error)
Hard-coding queries on the server and providing nested parameters is not really safe. To understand how an SQL injection attack works, it was very useful to me to read through the following article:

http(colon)//www.unixwiz.net/techtips/sql-injection.html

There is another SQL injection attack 'vector' - i.e.., 'method' - which is not covered in that article, called a "second-order attack", which is when the SQL query is stored in the database, and then activated by a later query. This is better described here:

http(colon)//www.codeproject.com/Articles/9378/SQL-I ... ow-to-Prev
bangkok wrote:-a sanitazition system, adapted to each type of query and/or type of parameters
There is no general "sanitiseSQL" or "encodeSQL" function, although these may be relatively trivial to program in LiveCode, and having these may cover the vast majority of use-cases.
bangkok wrote:Regarding your encryption system... sounds nice but how do know that the user who identifies himself with login+password on the desktop app, will be the same who will send later a SQL query to the server ?
That is a separate issue. I developed something similar to what you described, which is my own 'session+cookie' library - which stores a user's session id on the database, and then associates other values - ie., 'cookies' - with that session as needed (like the user's IP, to help increase security).

Thank you for your suggestions and info. :-)

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

Re: How To Protect Against SQL Injection Attack?

Post by phaworth » Thu May 24, 2012 3:05 am

Hi Igor,
Livecode does provide a way to protect against SQL injection. If you use the form of revExecuteSQL that uses parameters and variable placeholders, the data is sanitized and protected against injection.

As an example, let's say you have variables t1,t2,t3, and t4 that hold the values of you want to use in an INSERT command. The revExecuteSQL call would look like this:

revExecuteSQL tConnectionID, "INSERT INTO TableA (Col1,Col2,Col3,Col4) VALUES (:1,:2,:3,:4)", "t1","t2","t3","t4"

The variable names must be in quotes. The value in the first variable in the list (t1) will be subsitiuted in place of ":1", the second variable into ":2", etc.

You can also use an array with keys 1,2,3,4 if you prefer and just specify the name of the array (in quotes) instead of a list of variables.

Hope that helps,

Pete

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Thu May 24, 2012 8:26 am

Hi,

The form of revExecuteSQL mentioned by Pete doesn't protect you from a SQL injection more than the form without reference to variables. As long as you're connecting directly from LiveCode to the MySQL server, you can execute only one line of SQL syntax at a time. Although this is a huge limitation that shouldn't exist, it does protect you against a SQL injection attack to some extend, because commands like "DROP TABLE" need to be executed in a separate line.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

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

Re: How To Protect Against SQL Injection Attack?

Post by phaworth » Thu May 24, 2012 6:18 pm

Mark,
You're right, Livecode flags an error if you try to execute multiple mySQL commands in one call, but that's not the only from of injection attack.

Sometimes an attack will change the WHERE clause of a SELECT to something other than it was intended to be, usually by tacking on something like "OR 1=1" or some other condition that always evaluates to true. I believe the parameterised version I mention does protect against that, and it also removes the need to do your own sanitizing with a Livecode handler.

Pete

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: How To Protect Against SQL Injection Attack?

Post by Mark » Thu May 24, 2012 6:22 pm

Hi Pete,

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.

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Post Reply

Return to “Databases”