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…!
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!
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.
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?
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.
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:
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.
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…
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.
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.
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.
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?
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?
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.
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.
