SQL Injection Prevention - How Good Are These Measures?
-
Some background: I have taken a few measures to prevent SQL injection on my PHP/MySQL setup (currently WAMP for development, but will be LAMP for production server): - In my users table, I have entered a "bad" user, with all the fields equal to 0. - this is the first user in the table - if a hacker tries to enter
' or ''='in the uname field: - (theoretically,) "bad" user will be first result, and - (theoretically,) # of results will also be greater than 1 (more than 1 user) - (theoretically,) the following code will prevent said hacker from gaining unauthorized access - mysql_real_escape_string() function will be used to escape input when site is launched, but right now it is not in use to allow testing of common SQL injection methods. - I have read that mysql_real_escape_string() has some vulnerabilities. - I know mysql_real_escape_string() is more secure than addslashes()./* // This block will be uncommented after development
$u = mysql_real_escape_string($_POST[uname]);
$p = mysql_real_escape_string($_POST[pass]); */// These lines will be replaced by commented block above after development
$u = $_POST[uname];
$p = $_POST[pass];// I could add " order by uid asc" to the following query, to ensure 'bad' user listed first if SQL injection occurs, and to prevent hacker from using order by
$query = "select uid,uname,fname,lname,email,phone,other,pass from ads.users where uname = '$u'";
$result = mysql_query($query);
$rows = mysql_num_rows($result);// if more than 1 row, SQL injection attempted (uname has 'unique' flag in database, so only safe to return 1 row)
if ($rows > 1) {
/* <<Do Stuff - Security Measures (ban IP, etc)>> */
die ("Error[20]: You have entered potentially harmful input. Security measures have -
Some background: I have taken a few measures to prevent SQL injection on my PHP/MySQL setup (currently WAMP for development, but will be LAMP for production server): - In my users table, I have entered a "bad" user, with all the fields equal to 0. - this is the first user in the table - if a hacker tries to enter
' or ''='in the uname field: - (theoretically,) "bad" user will be first result, and - (theoretically,) # of results will also be greater than 1 (more than 1 user) - (theoretically,) the following code will prevent said hacker from gaining unauthorized access - mysql_real_escape_string() function will be used to escape input when site is launched, but right now it is not in use to allow testing of common SQL injection methods. - I have read that mysql_real_escape_string() has some vulnerabilities. - I know mysql_real_escape_string() is more secure than addslashes()./* // This block will be uncommented after development
$u = mysql_real_escape_string($_POST[uname]);
$p = mysql_real_escape_string($_POST[pass]); */// These lines will be replaced by commented block above after development
$u = $_POST[uname];
$p = $_POST[pass];// I could add " order by uid asc" to the following query, to ensure 'bad' user listed first if SQL injection occurs, and to prevent hacker from using order by
$query = "select uid,uname,fname,lname,email,phone,other,pass from ads.users where uname = '$u'";
$result = mysql_query($query);
$rows = mysql_num_rows($result);// if more than 1 row, SQL injection attempted (uname has 'unique' flag in database, so only safe to return 1 row)
if ($rows > 1) {
/* <<Do Stuff - Security Measures (ban IP, etc)>> */
die ("Error[20]: You have entered potentially harmful input. Security measures haveTo prevent SQL injection all you need to do is escape and use good validation. Other stuff can be a waste of time.
Brad Australian The PHP MVP - Christian Graus on "Best books for VBscript" A big thick one, so you can whack yourself on the head with it.
-
To prevent SQL injection all you need to do is escape and use good validation. Other stuff can be a waste of time.
Brad Australian The PHP MVP - Christian Graus on "Best books for VBscript" A big thick one, so you can whack yourself on the head with it.
I usually use this function to prevent SQL injection, maybe it's useful for you :
function quote_smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not a number or a numeric string
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}
return $value;
}Sorry for my English. I'm a freshman .
-
To prevent SQL injection all you need to do is escape and use good validation. Other stuff can be a waste of time.
Brad Australian The PHP MVP - Christian Graus on "Best books for VBscript" A big thick one, so you can whack yourself on the head with it.
Bradml wrote:
Other stuff can be a waste of time
Like using parameters instead of inlining?
cheers, Chris Maunder
CodeProject.com : C++ MVP
-
Bradml wrote:
Other stuff can be a waste of time
Like using parameters instead of inlining?
cheers, Chris Maunder
CodeProject.com : C++ MVP
Exactly.
Brad Australian The PHP MVP - Christian Graus on "Best books for VBscript" A big thick one, so you can whack yourself on the head with it.
-
To prevent SQL injection all you need to do is escape and use good validation. Other stuff can be a waste of time.
Brad Australian The PHP MVP - Christian Graus on "Best books for VBscript" A big thick one, so you can whack yourself on the head with it.
What level of validation is enough? I think that using prepared statements with parameters is the safest way, what do you think ?
Hesham A. Amin My blog
-
Some background: I have taken a few measures to prevent SQL injection on my PHP/MySQL setup (currently WAMP for development, but will be LAMP for production server): - In my users table, I have entered a "bad" user, with all the fields equal to 0. - this is the first user in the table - if a hacker tries to enter
' or ''='in the uname field: - (theoretically,) "bad" user will be first result, and - (theoretically,) # of results will also be greater than 1 (more than 1 user) - (theoretically,) the following code will prevent said hacker from gaining unauthorized access - mysql_real_escape_string() function will be used to escape input when site is launched, but right now it is not in use to allow testing of common SQL injection methods. - I have read that mysql_real_escape_string() has some vulnerabilities. - I know mysql_real_escape_string() is more secure than addslashes()./* // This block will be uncommented after development
$u = mysql_real_escape_string($_POST[uname]);
$p = mysql_real_escape_string($_POST[pass]); */// These lines will be replaced by commented block above after development
$u = $_POST[uname];
$p = $_POST[pass];// I could add " order by uid asc" to the following query, to ensure 'bad' user listed first if SQL injection occurs, and to prevent hacker from using order by
$query = "select uid,uname,fname,lname,email,phone,other,pass from ads.users where uname = '$u'";
$result = mysql_query($query);
$rows = mysql_num_rows($result);// if more than 1 row, SQL injection attempted (uname has 'unique' flag in database, so only safe to return 1 row)
if ($rows > 1) {
/* <<Do Stuff - Security Measures (ban IP, etc)>> */
die ("Error[20]: You have entered potentially harmful input. Security measures haveSQLi is best handled using the database's native escaping routines and not just relying on addslashes() -- there is actually a way to circumvent addslashes from what I remember. Filtering is probably a good practice as well. To avoid escaping, you could just use PDO and prepared statements which handles the escaping for you automagically as well.
I'm finding the only constant in software development is change it self.