How to prevent mysql injection

[mr_fermi]

Dear Everyone,

I have this form that consits of text fields and combo boxes. This is form is filled by a user and then the filled data is pushed in to a database, however i am worried about the security. Does anyone know how can i validate the input fields (mainly the text area fields) to prevent anyone from doing a mysql injection??? Does anyone have any idea of how to prevent mysql injection?? Is there a ready made code or some useful php functions that prevent that automatically????

Yours
Mohamed

[Yelgnidroc]

I use this:


// escape all form data to prevent sql injection

require_once('mysql_connect.php');

function escape_data($data)
{
global $dbc;

if(ini_get('magic_quotes_gpc'))
$data=stripslashes($data);

return mysql_real_escape_string(trim($data),$dbc);
}

foreach($_POST as $this_variable_not_used)
escape_data($_POST);



No guarantees that it will suit you, but I think that it's pretty robust.

[felgall]

The best thing to use is custom validation of each field once it reaches the server to ensure that the contents are valid for whatever the field is supposed to contain eg. if the field is supposed to contain a number use is_numeric(), if it is an address field then use a regular expression to make sure that all the characters entered are valid for an address, and so on. The more specific you can make each field validation the more secure your script will be.

[mdjo]

I'm not sure what you mean by "injection". Do you mean as in, The user enters values that corrupt your SQL? Like, I came across a mention in a book once that the author figured out that a certain popular website validated log ins by creating a SQL statement on the fly that went something like this:

String sql="select * from userlist where userid='"+userid+"' and password='"+password+"'";

The idea being that if you entered, say "bob" and "foobar", it would generate:

select * from userlist where userid='bob' and password='foobar'

If a record was found, they let you in.

So he entered a password of

xyz' or 'x'='x

Figure out what SQL will result and you'll see how secure their system was.

But anyway, this is easy to prevent. All you have to do is NOT take the lame approach of blindly slapping single quotes around all strings, but rather write a function that searches strings for embedded single quotes and escapes them properly. In most DB's this means doubling them. I know I ran into one somewhere along the line where you had to put a backslash in front of the quote, but the principle is the same.

In all my programs where I generate SQL, I include a function that accepts a string, puts single quotes around it, and does whatever the database engine requires to escape embedded quotes. I tend to put this function in a class called Db and call it q because I want something short because I use it a lot.

So basically, Db.q("hello") simply returns 'hello'.
Db.q("O'Malley") returns 'O''Malley'.

I overload this function to accept ints. q(int x) just returns Integer.toString(x). No need to quote int's, but it's easier to call the function on everything and not have to think about it. Oh, and I overload it to accept a Date, and create the JDBC escape sequence for a date. (You know, the {d'2007-10-04'} thing.) Finally, I overload it to accept an Object, and q(Object o) returns q(o.toString()), to handle almost anything else. That's also a good place to check for nulls. 99% of the time I want nulls to go in as zero-length strings, definately not as the word "null", so I can do that once.

Depending on the database engine, it might be a good idea to check for anything with a character code less than space, as these are unlikely to be valid characters. Though I've never seen anything like this create a security hole, I can't say that I've thoroughly investigated.

Then my SQL-generation code tends to look like this:

String sql="select name,tx_amt from account where account_number="+Db.q(current_account)+" and tx_date<"+Db.q(end_date);

As a previous poster noted, for truly robust error checking, check every field. If you're building command lines or some such where lots of characters can cause you trouble, don't try to check for all the troublesome characters and screen those out: check for the known not-troublesome characters and screen those in. That is, don't exclude quote, newline and ampersand, exclude anything that is NOT a letter, digit, or specified list of known safe punctuation. This is a good practice any place where the list of legal characters is limited. I routinely screen email addresses for anything other than letter, digit, underscore, hyphen, period, and at sign, and if I really want to be careful I check for things like more than one at and that sort of thing.

[mdjo]

I'm not sure what you mean by "injection". Do you mean as in, The user enters values that corrupt your SQL? Like, I came across a mention in a book once that the author figured out that a certain popular website validated log ins by creating a SQL statement on the fly that went something like this:

String sql="select * from userlist where userid='"+userid+"' and password='"+password+"'";

The idea being that if you entered, say "bob" and "foobar", it would generate:

select * from userlist where userid='bob' and password='foobar'

If a record was found, they let you in.

So he entered a password of

xyz' or 'x'='x

Figure out what SQL will result and you'll see how secure their system was.

But anyway, this is easy to prevent. All you have to do is NOT take the lame approach of blindly slapping single quotes around all strings, but rather write a function that searches strings for embedded single quotes and escapes them properly. In most DB's this means doubling them. I know I ran into one somewhere along the line where you had to put a backslash in front of the quote, but the principle is the same.

In all my programs where I generate SQL, I include a function that accepts a string, puts single quotes around it, and does whatever the database engine requires to escape embedded quotes. I tend to put this function in a class called Db and call it q because I want something short because I use it a lot.

So basically, Db.q("hello") simply returns 'hello'.
Db.q("O'Malley") returns 'O''Malley'.

I overload this function to accept ints. q(int x) just returns Integer.toString(x). No need to quote int's, but it's easier to call the function on everything and not have to think about it. Oh, and I overload it to accept a Date, and create the JDBC escape sequence for a date. (You know, the {d'2007-10-04'} thing.) Finally, I overload it to accept an Object, and q(Object o) returns q(o.toString()), to handle almost anything else. That's also a good place to check for nulls. 99% of the time I want nulls to go in as zero-length strings, definately not as the word "null", so I can do that once.

Depending on the database engine, it might be a good idea to check for anything with a character code less than space, as these are unlikely to be valid characters. Though I've never seen anything like this create a security hole, I can't say that I've thoroughly investigated.

Then my SQL-generation code tends to look like this:

String sql="select name,tx_amt from account where account_number="+Db.q(current_account)+" and tx_date<"+Db.q(end_date);

As a previous poster noted, for truly robust error checking, check every field. If you're building command lines or some such where lots of characters can cause you trouble, don't try to check for all the troublesome characters and screen those out: check for the known not-troublesome characters and screen those in. That is, don't exclude quote, newline and ampersand, exclude anything that is NOT a letter, digit, or specified list of known safe punctuation. This is a good practice any place where the list of legal characters is limited. I routinely screen email addresses for anything other than letter, digit, underscore, hyphen, period, and at sign, and if I really want to be careful I check for things like more than one at and that sort of thing.