Page 1 of 1
SQL Injection Protection
Posted: Thu Jan 10, 2013 2:32 pm
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.
Re: SQL Injection Protection
Posted: Thu Jan 10, 2013 3:09 pm
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.
Re: SQL Injection Protection
Posted: Thu Jan 10, 2013 3:14 pm
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.
Re: SQL Injection Protection
Posted: Thu Jan 10, 2013 3:26 pm
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.
Re: SQL Injection Protection
Posted: Thu Jan 10, 2013 4:07 pm
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?
Re: SQL Injection Protection
Posted: Thu Jan 10, 2013 7:20 pm
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.
Re: SQL Injection Protection
Posted: Fri Jan 11, 2013 3:05 am
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?
Re: SQL Injection Protection
Posted: Fri Jan 11, 2013 4:11 am
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.
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.
Re: SQL Injection Protection
Posted: Fri Jan 11, 2013 4:51 am
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.
Re: SQL Injection Protection
Posted: Fri Jan 11, 2013 4:56 am
by spacebiscuit
I believe you can choose to encode the vars, for example with Json.
Re: SQL Injection Protection
Posted: Fri Jan 11, 2013 1:16 pm
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.
Re: SQL Injection Protection
Posted: Tue Feb 05, 2013 6:16 pm
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