SQL Injection Protection
Moderator: General Moderators
-
spacebiscuit
- Forum Contributor
- Posts: 390
- Joined: Mon Mar 07, 2005 3:20 pm
SQL Injection Protection
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.
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
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.
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
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.
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
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.
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
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?
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
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.
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
In my case it throws an error. Consider this:
If you attempt to inject the url:
You then catch the malicious code which is stripped out:
The query then becomes:
When the statement is executes an error occurs because the id is missing:
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?
Code: Select all
SELECT * FROM `prodcts` WHERE id = $id_varCode: Select all
index.php?id_var=2&+'UNION+SELECT+BLAH+BLAH'Code: Select all
$_GET['id_var']=filter_var($_GET['id_var'], FILTER_VALIDATE_INT)Code: Select all
SELECT * FROM `prodcts` WHERE id = Code: Select all
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/your/script on line 1Re: SQL Injection Protection
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.
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'];- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: SQL Injection Protection
If you are refering to ajax, it is still an http request.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?
“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
I believe you can choose to encode the vars, for example with Json.
Re: SQL Injection Protection
Doesn't matter, it's still an HTTP request. And like all HTTP requests they can be forged. Easily.spacebiscuit wrote:I believe you can choose to encode the vars, for example with Json.
- mecha_godzilla
- Forum Contributor
- Posts: 375
- Joined: Wed Apr 14, 2010 4:45 pm
- Location: UK
Re: SQL Injection Protection
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:
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
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());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