Important: Secure your SQL queries!!!

[MarcusJT]

I thought I'd better make sure that everyone is aware of a particular class of security vulnerabilities called malicious SQL code injections. While there are many other security vulnerabilities, these are particularly dangerous - left unchecked, a malicious user could access or otherwise manipulate any data in the SQL Server, as well as execute DOS commands, etc.

These vulnerabilities are nothing new, but security is usually the least of most developers' worries, and some developers may not even have considered this angle.

If you are not already familiar with this topic, it is imperative that you read up pronto - you could be unwittingly putting all of your live DB data at risk, and possibly the server itself too!

Please note that although most exploits using this technique target MS SQL Server, some can be applied to Access (and other DBs) too, so you're not necessarily invincible just because you've got a small site running off Access....!

Here's a few sites to start you off (!):
http://www.google.com/search?q=SQL+injection

Hot off the presses - four rules for SQL Injection protection:
http://msdn.microsoft.com/en-us/libr...SQL.80%29.aspx



M@rco

[MattR]

Here's a good whitepaper (maybe we subscribe to the same newsletters? ):
http://www.spidynamics.com/whitepape...LInjection.pdf

[bbolte]

i've looked through a couple of the articles (not the whitepapers yet) and wondered if this can also be passed through a querystring in the url? would that mean that we should validate any querystring variables? that is something i've never thought about before - thanks for the heads up guys!

[DaveMaxwell]

Originally posted by bbolte
i've looked through a couple of the articles (not the whitepapers yet) and wondered if this can also be passed through a querystring in the url? would that mean that we should validate any querystring variables? that is something i've never thought about before - thanks for the heads up guys!

Absolutely, you should be validating your querystrings at all times! Always ensure that they are numeric if you're expecting numerics, etc.

[bbolte]

in terms of security (as the articles discuss), what should i be validating against? i understand checking for datatypes and single apostrphies, etc. but what else? whether in querystrings or form fields, what should i be doing?

[JimBolla]

Mainly I started useing stored procedures and the ADODB.Command object with explicitly defined parameters. This garuntees that any SQL code a hax0r injects to your application will simply be added as a parameter value and will either error out due to a type mismatch or will be stored as string data. This is a very good defense (in my opinion anyways) against SQL injection.

I also consider all Querystring/Form/Cookie items to be subject to tampering. This means they get checked for datatype and sometimes range. If one of the values fails the test then you should either show them an error message or, depending on the nature of the app, sometimes you can just "fix" the data and move along.

[MattR]

Does ADO sanatize (e.g. escape single quotes) in parameters? If so, then I would say that is all you need to do. If you can pass in single quotes and break the SQL then there's a significant hole right there!

Same thing with integer values -- can you pass in an integer like this:

45, dfklsjf

Or will it error?

[MarcusJT]

Alas, ADO does not sanitize data - problems caused by strings containing unescaped quotes are posted on a regular basis here. Likewise with your integer example - it's the same problem as trying to do this in an ASP script:

<%
i = CInt("45, dfklsjf")
%>

So, we the developers are responsible for validating and sanitizing (where possible) all user-entered data in order to ensure that such problems do not occur during the course of normal data entry, and especially to ensure that inherent security vulnerabilities are shielded from malicious users.


M@rco

[EvilDoppler]

I've always made it a habit to validate my input before subjecting it to the database..was taught a long long time ago to be careful about that but still shocked me to see some of my code being uhmmm...lets just say..less than secure...one problem was integers added to text fields (well i wanted only numbers but the text field could of course contain anything)..

simple php validation (which i'm not sure was in the article or not) is is_int()..

i've become slack...

anyways, the article (downloadable pdf whitepaper) kinda perked me back up on my toes again..

nice article!!

[MarcusJT]

Although it doesn't excuse you NOT using data validation before inserting it into the DB, using the ADODB.RecordSet .Update and .AddNew methods in conjunction with their optional parameters (an array of field names and an array of field values) can make your scripts a lot more impervious, since the data being inserted is never interpreted by the DB, and so defeats the particular type of SQL injection vulnerability that we have been discussing.

FieldsList = "ID|Name|Email"
ValuesList = "3|M@rco|marcojt@antisocial.com"

RS.AddNew split(FieldsList,"|"),split(ValuesList,"|")

(Of course, the arrays can be generated in any way you want, not just the way I have above)

This has been my preferred method of inserting/updating data for ages - I rarely need to use INSERT or UPDATE statments at all!


M@rco

[shane]

Originally posted by M@rco
Alas, ADO does not sanitize data - problems caused by strings containing unescaped quotes are posted on a regular basis here.

ADO will deal with apostrophes if you use a Command object, with Parameters, even if you use an sql string or stored procedure.

<snip/>

Using Parameters has also got the advantage that you can specify the data type and size. If you use SP's as well then it's easier to configure the security access to the DB.

You'd still have to validate any data before though.

[weirdbeardmt]

I suppose the biggest thing at the mo for all PHP programmers, something that the majority of scripts round SPF currently don't do, is learning to program with register_globals off. That is surely the easiest way to use malicious query strings that attempt to overcome most attempted securities.

Rather than using something like

if(isset...) // or
if ($variable=="value") 

use the built in arrays like _POST etc etc.

[MarcusJT]

This is also excellent reading material on the subject (although presented from an ASP point of view, 99% of it applies to every server-side scripting language out there):
http://www.adopenstatic.com/resource...Validation.asp


M@rco

[Technosailor]

okay, so to pick ya'lls brains a little....how do you validate a text field? If there is no limits on a text field, how do you validate it? I'm using MySQL but this pertains to any db. I also use PHP. In MySQL, varchar can only have 255 characters....so anything more than that, say...an article, requires a text field. How do you make sure no malicious code is being passed there?

Sketch

[MattR]

I'm confused -- you do the same thing for all character fields, e.g.

mysql_query( "INSERT INTO mytable
                  VALUES( $someint
          " . addslashes( $somecharacter ) . ",
          " . addslashes( $lotsoftext    ) . " )" ); 

[MarcusJT]

Hot off the presses:
http://www.4guysfromrolla.com/webtech/061902-1.shtml

This one from Sketch too:
http://www.webmasterbase.com/article/794


M@rco

[MarcusJT]

Some more:
http://www.securiteam.com/securityre...DP0N1P76E.html
http://www.securiteam.com/securityre...UP010A6AA.html
http://www.securiteam.com/securityre...GP0E2K7FO.html


M@rco

[Toly]

http://www.webmasterbase.com/article/794

I got to check that article too but it was for asp and ms sql.

Thanks for the other two articles. Still i'm not very sure of how to prevent these attacks.

I think you were discussing something about validating every variable that comes from the url. For instance if i have the following url:

http://www.mysite.com/update.php?id=12&name=toly

Lets just say i want to update a record on my database with the id equal to 12 and name equal to toly. If i make my script to check that the variable ID only contain numbers and the variable name only contain letters, would that be enough?

[MattR]

Toly for the number value yes that is acceptable.

However for the character string you must make sure to escape end string characters so commands will not be processed.

For example, in virtually every RDBMS you use single quotes around strings. Let's say I was logging in to this forum system and internally the SQL is like this:

SELECT userid
  FROM user
 WHERE username = '$username'
   AND password = '$password'

Typically the application code then says if the userid exists, then we have a match, so go ahead and log them in.

However, if I entered for my password:
' or 1=1 or password = '

Then the SQL would look like this:

SELECT userid
  FROM user
 WHERE username = 'MattR'
   AND password = '' or 1=1 or password = ''

Since 1 is equal to 1, it will think I matched and log me in. If you escape the string (T-SQL style in this example), it will look like this:

SELECT userid
  FROM user
 WHERE username = 'MattR'
   AND password = ''' or 1=1 or password = '''

Which will check your password being the entire string, and will not match.

[Toly]

Hi,

Thanks for the reply Matt.

So what you are trying to say is that i should use two single quoates rather than one to avoid commands to execute?

For instace:

SELECT * FROM users
   WHERE username = '$username'
   AND password = ''$password'' 

I don't know if this is what you meant. I have a login section on my site and i tried to put the thing you wrote: ' or 1=1 or password = ' in the password field but it didn't give me access.

Thanks again.

[bbolte]

Originally posted by Toly
So what you are trying to say is that i should use two single quoates rather than one to avoid commands to execute?

no, what he is saying is to feed all the data you're gathering from the form through some kind of procedure that will replace all single quotes with double single quotes. here is a simple one for ASP/VBScript (i'm sure you have something equivalent in PHP):

Function fixQuotes(theString)
	fixQuotes = Replace(theString, "'", "''")
End Function

[SpazBoy]

"default deny regular experssion" from the SPI dynamics solutions section.

Does that mean to select a set amount of characters so your 'user input' (whatever it might be) will be removed of any qutoes or other symbols that you dont want?

I was wondering if this solution will work. (I am sure someone has thought of it already and it might sound really dumb since I am a newbie) Let's say you have an admin page for your webmaster to log into to perform multiple sql tasks. Of course, general users and the public doesnt need to enter the page. Therefore, instead of just using sql injection prevention methods, cant you limit access to certain IP addresses. I am aware some ISP give dynamic IPs, but for some ADSL/DSL companies you get a static ip. I read some where you can log the users ip using php. All that has to be done is to store the static ip (the computer that the admin uses) somewhere safe. Everytime someone tries to go to the admin page, their ip will be matched against the stored one. If its different, automatic redirect so the no input can even take place.

I dont know if that works, I just thought of it out of the blue in light of reading this article. Don't laugh if its super stupid, I am new to coding security and all. Tell me if it works.

Spaz

[MarcusJT]

It's not a stupid suggestion at all - it is indeed possible to restrict access to something by (virtually) any criteria, and a system such as that which you suggest could indeed be implemented if desired.

However, in practice, I don't think that it would be a good idea at all, since one of the best features of any remote admin system is the ability to log on from anywhere and fix/alter something, but if you've restricted admin access to only a single IP, then you're screwed if you're located anywhere else when you need to make the change!!

Hence locking down admin access to only a single PC or IP address is not a good idea for that reason alone (and I'm sure that there are plenty of others).

[SpazBoy]

True... It will be a big problem if the admin doesnt have a static ip. Come to think of it, most of the time admins might need to change stuff when away from the office.

Another question:
After reading some of the articles in this post, it makes me think hard to prevent sql injection attacks.

If I am remember correctly, the "Dynamic something (I forgot the second part of the company name. I just know its one of the first articles posted)" said its simply not enough by removing single quotes, backslashes and all. So what else can we do?

By removing all the possible symbols needed for "fooling" the code, how can the attacks take place?

There are many suggestions to actually limit or set a range of characters or values to be used. Something like (according to my memory):
[a-z], [A-Z], [0-9]
Won't that be limiting if a user requires " ' " in their search?

Spaz

[bbolte]

i think you would have to evaluate what data you are getting. if it is only supposed to be numeric, strip everything except numerals (use regular expressions). <snip/>