SQL Injection Protection

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

SQL Injection Protection

Post by spacebiscuit »

Hi,

I'm trying to tighten up my site to protect against SQl injection attacks.

What are your opinions on filtering variables passed via url. I have read about the filter_var function, is this sufficient protection?

Thanks in advance.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Injection Protection

Post by requinix »

I've never been one to use filter_var() because it's just as easy to call whatever actual function you want. Maybe for an email address but I don't like the regex it uses.

Anyways filter_var() is fine for numbers but will not protect you against strings. You still have to use prepared statements or *_real_escape_string() on them.
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: SQL Injection Protection

Post by spacebiscuit »

I'm mostly dealing with numbers so filter_var() will work.

Any suggestions as to how to handle a failed filter attempt, should I break, re-direct the 'user'? It looks a bit messy if the injection attempt fails because the original statement trips over and the full path url is output to screen which in itself is a security issue I believe.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Injection Protection

Post by requinix »

Injection failures shouldn't cause any problems or errors whatsoever...

For numbers I tend to just cast to int or float and proceed onwards. If they typed something that was almost a number, like "5five", then the result will be 5 and all is well. Meanwhile if they entered "five" then it'll use 0 and that shouldn't find/change anything. At most I'll show a "not found" message.
The other valid alternative is warning the user that the input wasn't valid and prompting again. Most anything else I can think of, particularly displaying some kind of "hacking attempt" message, is silly and potentially harmful.
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: SQL Injection Protection

Post by spacebiscuit »

What I meant was, if I enter a url with an SQL injection attempt and use filter_var() it trips the original SQL statement up and an error is output to screen.

Looking further ahead, I presume it is more secure to use something like JQuery to execute MYSQL? not only is it more elegant but variables are not sent via the http requests?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Injection Protection

Post by requinix »

It's more of a statement: injection shouldn't result in any errors. There must be something up with your code.

As for jQuery and MySQL, I don't know what you're thinking but I'm sure it's very bad. The only thing talking to MySQL should be your PHP code.
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: SQL Injection Protection

Post by spacebiscuit »

In my case it throws an error. Consider this:

Code: Select all

SELECT * FROM `prodcts` WHERE id = $id_var
If you attempt to inject the url:

Code: Select all

index.php?id_var=2&+'UNION+SELECT+BLAH+BLAH'
You then catch the malicious code which is stripped out:

Code: Select all

$_GET['id_var']=filter_var($_GET['id_var'], FILTER_VALIDATE_INT)
The query then becomes:

Code: Select all

SELECT * FROM `prodcts` WHERE id = 
When the statement is executes an error occurs because the id is missing:

Code: Select all

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/your/script on line 1
Jquery has a function that enable you to call php without having send and receive request via an http. For example you could have a web form, when the submit button is clicked the php is executed but the page is not reloaded and data is not sent through GET or POST vars. I imagine that is far more secure, why is it bad?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Injection Protection

Post by requinix »

FILTER_VALIDATE_INT will validate the data. If the data is not valid then filter_var() will tell you it was not (by returning false). On the other hand FILTER_SANITIZE_NUMBER_INT will sanitize the data.

As one who doesn't use filter_var(), as already professed, I would simply suggest you cast to int.

Code: Select all

$id_var = (int)$_GET['id_var'];
On that note it's bad form to alter the original $_GET and $_POST data. Leave it as-is and use a variable for the modified version.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL Injection Protection

Post by social_experiment »

spacebiscuit wrote:Jquery has a function that enable you to call php without having send and receive request via an http. For example you could have a web form, when the submit button is clicked the php is executed but the page is not reloaded and data is not sent through GET or POST vars. I imagine that is far more secure, why is it bad?
If you are refering to ajax, it is still an http request.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: SQL Injection Protection

Post by spacebiscuit »

I believe you can choose to encode the vars, for example with Json.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Injection Protection

Post by requinix »

spacebiscuit wrote:I believe you can choose to encode the vars, for example with Json.
Doesn't matter, it's still an HTTP request. And like all HTTP requests they can be forged. Easily.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: SQL Injection Protection

Post by mecha_godzilla »

Hi,

Just to add to what requinix is saying, it's very easy to use something like cURL to generate your own HTTP requests - all someone has to do is look at your page source to try and work out what's going on.

A "sanitizing" strategy would include the following:

1. Make sure the data is in the expected format and of the correct length - frameworks like Yii refer to the database schema to determine how long an ID should be and what data type it is, but you can do this manually if you need to. Also keep in mind that multibyte encoding will result in a different variable length being received.

2. Don't have any code in your site that looks like this:

Code: Select all

$result = mysql_query($sql, $conn) or die(mysql_error());
This might give the attacker lots of useful information about what your query is doing if it fails. I usually replace die() with a redirect to a "Sorry, an error occured" page which also triggers an email to me so I know that it's happened.

3. As previously suggested, always use *_real_escape_string() as this beats writing your own sanitizing code hands-down.

4. Also as suggested, use prepared statements or stored procedures to really lock-down the query. Although it's now deprecated, mysql_query() only allows one query to be sent from it so attackers can't add ";" to the end of your intended query to append more queries.

5. This may or may not be appropriate for your application, but all of my login scripts hash the username/email address in addition to the password and then compares them with hashed versions in the database - this means that (in theory) any kind of SQL injection would be defeated because whatever the attacker sends just ends up as a hash.

HTH,

Mecha Godzilla
Post Reply