Page 1 of 1
sql injection
Posted: Thu Apr 01, 2004 5:52 pm
by josh
I have read stuff about this before and stuff I have seen hasn't worked... what can i do for example if $user is going into my querry?
Code: Select all
<?php
// replace a single ' with nothing ("")
$user=strreplace($user,"'","");
?>
something around those lines possible?
Posted: Thu Apr 01, 2004 6:21 pm
by kettle_drum
To stop sql injection you just need to add slashes to quotes, both " and ', and then in your sql query be sure to place '' around the variable, so you have to escape those quotes first inorder to perform sql injection.
mysql_query("SELECT * FROM blah WHERE value = '$value'");
You can then also check $value for other signs of sql injection such as the use of "UNION", "JOIN". And if your really paroinod then just refuse to load the page if the var you get from the user has ANY sql keywords in - such as select, delete, truncate, union, where, like etc.
Also be sure to protect against XSS (cross site scripting) as well - which follows the same kinda rules - just change all <>?{} etc into the html entities.
Posted: Thu Apr 01, 2004 8:00 pm
by josh
ok but how would i change these values or detect them?
Posted: Thu Apr 01, 2004 8:06 pm
by qads
[php_man]addslashes[/php_man]
[php_man]htmlspecialchars[/php_man]
php.net is your friend

Posted: Thu Apr 01, 2004 8:35 pm
by kettle_drum
OK, use the style of the mysql_query i posted before with '$value' in the single quotes, then before you do that run it through a function like this:
Code: Select all
function xss_check($string){
$string = htmlspecialchars($string, ENT_QUOTES);
return $string;
}
this is good for both sql injection and xss provention. Then as i was saying before check to see if the user could be trying some sql injection by doing something like:
Code: Select all
function sql_check($string){
$keywords = array("select", "insert", "union", "delete", "truncate", "grant");
foreach($keywords as $q){
if(ereg($q, $string)){ //possible sql injection
echo "The data you submitted to us looks suspicious and has been logged, please return to the previous page.";
//do some logging of IP address here etc
exit; //stop the page from loading
}
}
}
That should check the input for the values in the array - the sql keywords - and if there is any it will stop the page from loading. Of course this may cause some valid users to get this message, so you may need to make a better check - as this is only basic.
Posted: Thu Apr 01, 2004 9:10 pm
by d3ad1ysp0rk
What happens if they go
"april 1st:
I cant wait to select which trip im going on for the school unit! it's gonna be so much fun! but i gotta go, leave some comments!"
"The data you submitted to us looks suspicious and has been logged, please return to the previous page."
"huh????"

Posted: Thu Apr 01, 2004 9:30 pm
by Illusionist
ya good point punk.
kettle_drum, as long as you escape all ' and " it wont matter about any SQL they input. So that second function is just a waste, pretty much. I can think of pretty legit sentences using all those words that would have nothing to do with trying to inject SQL or anything.
Posted: Thu Apr 01, 2004 10:06 pm
by kettle_drum
Yes, which is why i said that its only basic and is just to show what sort of things you can do to prevent it. But its a good base for provention and protection. Add a few more checks to look for other sql like text, throw in a few reg expressions to see if "SELECT * FROM" is in there, or if it looks like the user is trying to escape the current query.
Its always better to be safe than sorry - just look at the last month in bugtraq, phpbb has had several sql injections discovered and so has phpnuke, mambo, several shopping carts, phpnuke modules the list goes on.
By checking for sql like strings in the data posted your just adding that extra bit of protection that may come in handy one day - as adding slashes may provent it today, but there may be other ways to exploit it in a few weeks or months.
Its a good pratice that people should start to put into use to create secure scripts, or at least to be notifed when suspected sql injection is detected - see the warning when it happens, so you can fix the hole and see what the user has injected so you can act against it.
Posted: Fri Apr 02, 2004 6:36 am
by scisoft
Good post. I have been using the stripslashes for some time, but was not aware that htmlspecialchars are so important. Besides this plus avoiding global variables and using IsUploadedFile, are there some other basic safety precautions you should use?
Thank you.
Posted: Fri Apr 02, 2004 9:25 am
by kettle_drum
You basically just need to check anything that the user submits to the site either through the url(GET), POST or COOKIES as if you print any of these variables to the screen without checking and removing html then your site will have a XSS hole (cross site scripting) - which can lead to cookie stealing, which means they can fake a cookie and log in as that person.
There is then the risk of sql injection, where a user sends code to your server which will be inserted into the database, and once again this can be achieved by just putting $_POST[var], $_GET[var], $_COOKIE[var] straight into an sql query.
Posted: Wed Apr 07, 2004 11:11 am
by phait
Hi,
I sometimes seem to struggle with this and sometimes I 'get it'. Anyways, I just wondered if this function seemed pukka:
Code: Select all
function makeInputSafe($string) {
if(get_magic_quotes_gpc()) {
$string = htmlspecialchars($string, ENT_NOQUOTES);
} else {
$string = htmlspecialchars($string, ENT_QUOTES);
}
return $string;
}
This way, if gpc quotes is enabled then there is no need for us to quote them again and if it isn't set then we do quote them to save ourselves from script kiddie hell. If this is wrong, please let me know.
Also... in terms of protecting against specfic SQL keywords, doesn't the attacker attempt to truncate the string by using a semicolon ';' ? So they might try something like the user field having the following data:
$value = bogusName;delete from tablename
if we then take the SQL query which is:
Code: Select all
mysql_query("SELECT * FROM blah WHERE value = '$value'");
at runtime it would actually look like:
Code: Select all
mysql_query("SELECT * FROM blah WHERE user='bogusName;delete from tablename'");
And if this is correct[?] then you could have the keywords array actually look for SQL statements preceeded by a semicolon. For example ";select",";join",";delete" as these would surely be more an indicator of some monkey rather than an innocent user inputting data?
Someone please let me know if this is close or am I just smoking too much crack? I need to know once and for all.
