Preventing SQL Injection

[Petsmacker]

Hey, I'm almost certain my site has a few security holes and have begun work trying to patch them up - one way is by preventing SQL Injections.

The PHP.net site recommends this kind of strategy:
[code]<?
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}?>[/code]

This is a PHP file included on every page of my site:
[code]<?
function stripslashes_gpc( &$var ){
while( list( $key, $value ) = each( $var ) ){
if( is_array( $var[$key] ) ){
stripslashes_gpc( $var[$key] );
}
else
{
$var[$key] = stripslashes( $value );
}
}
reset( $var );
}

if( count( $_GET ) > 0 ){stripslashes_gpc( $_GET );}
if( count( $_POST ) > 0 ){stripslashes_gpc( $_POST );}
if( count( $_COOKIE ) > 0 ){stripslashes_gpc( $_COOKIE );}
?>[/code]

How could I possibly implement the above code into this file so that $_GET, $_POST and $_COOKIE variables are protected. Is there any way of doing this - by only editing this or a few files rather than systematically going through every page on my site and editing them?

[corbin]

I normally do something like
[code=php:0]
foreach($_POST as $k => $v) {
$_POST[$k] = addslashes(stripslashes($v));
}
[/code]
for post get and cookie... That way if theyre already slashed then it removes the slashes before it adds the new ones and theyre all mysql query safe.

[Petsmacker]

And that completely stops SQL Injections?
One example PHP.net was this:


$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";
mysql_query($query);

// We didn't check $_POST['password'], it could be anything the user wanted! For example:
$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

SELECT * FROM users WHERE user='aidan' AND password='' OR ''=''

Which would allow anybody to login without a password. (Though I'm thinking about this in terms of creating my own forum)
- So something like the above couldn't happen with your code?

[roopurt18]

corbin, what if your user is intentionally trying to add a double back slash?

[corbin]

echo addslashes(stripslashes(\\'or a = a\\'));  Would return \\'or a = a\\'  which would still cancel out in mysql... I think... I had never thought of that before... you could do

[code=php:0]
foreach($_POST as $k => $v) {
str_replace("\\", "", $v);
$_POST[$k] = addslashes(stripslashes($v));
}
[/code]

Which would remove all slashes before adding new ones so even \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'content\\\\\\\\\\\\\\\\\\'  would return \'content\'

[Petsmacker]

I don't really want to have to automatically edit what my members post simply because they may be a threat. If somebody for whatever reason did want to post: \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'content\\\\\\\\\\\\\\\\\\, then it would come out like that but of course, I still don't want my site to be threatened. There must be a way for posts to come out whole and good - even with protection added on.

I found this script:

function quote_smart($value)
{
  if( is_array($value) ) {
      return array_map("quote_smart", $value);
  } else {
      if( get_magic_quotes_gpc() ) {
          $value = stripslashes($value);
      }
      if( $value == '' ) {
          $value = 'NULL';
      } if( !is_numeric($value) || $value[0] == '0' ) {
          $value = "'".mysql_real_escape_string($value)."'";
      }
      return $value;
  }
}

Do you think if I changed the :
[i]if( count( $_GET ) > 0 ){stripslashes_gpc( $_GET );}
if( count( $_POST ) > 0 ){stripslashes_gpc( $_POST );}
if( count( $_COOKIE ) > 0 ){stripslashes_gpc( $_COOKIE );}[/i]

To:
[i]if( count( $_GET ) > 0 ){quote_smart( $_GET );}[/i]
...and so on - would it work?

[corbin]

Gonna test some stuff real quick i guess :P

[corbin]

Ok turns out my original script can handle user \'s.  (my .ini is set to automatically add slashes on post get and cookie {always forget what the setting is called} so i always do stripslashes too)

[code=php:0]
<?
mysql_connect("localhost", "*******", "********");
mysql_select_db("test");
if($_POST) {
foreach($_POST as $k => $v) {
$_POST[$k] = addslashes(stripslashes($v));
}
$sql = "SELECT * FROM users WHERE `uname` = '{$_POST['name']}' and pass = '{$_POST['pass']}'";
$q = mysql_query($sql);

echo $sql . "<br>";
$n = @mysql_num_rows($q);
is_numeric($n) ? NULL : $n = 0;
echo "number of rows: " . $n . "<br>";
echo "Name input: " . stripslashes($_POST['name']) . "<BR>";
echo "Pass input: " . stripslashes($_POST['pass']) . "<BR>";

}
?>
<form action="" method="post">
name: <input type="text" name="name"><BR>
pass: <input type="text" name="pass"><BR>
<input type="submit" name="sub" value="Submit">
</form>
[/code]

If a user inputs \\\\  if you strip the slashes off the post variable (once it adds them) it will return \\\\

Script mentioned above running live at http://corbin.no-ip.org/sql/  (dont get excited if you find a security hole, the user the script is running on just has select privs on that DB :P

[Petsmacker]

Alright, my host automatically adds \'s to posts too but I don't know if my original question has been answered.

If someone was to add: ' OR ''='
Into a password field like it says in my 2nd post, would it pose a threat - would it pose a threat with the original script I posted? Or should I change it to the way you have yours? Would it make a difference?

[The Little Guy]

http://us3.php.net/manual/en/security.database.sql-injection.php


Never connect to the database as a superuser or as the database owner. Use always customized users with very limited privileges.

Check if the given input has the expected data type. PHP has a wide range of input validating functions, from the simplest ones found in Variable Functions and in Character Type Functions (e.g. is_numeric(), ctype_digit() respectively) and onwards to the Perl compatible Regular Expressions support.

If the application waits for numerical input, consider verifying data with is_numeric(), or silently change its type using settype(), or use its numeric representation by sprintf().

[corbin]

I personally do what The Little Guy posted along with adding slashes... For example if I have a page that uses id as a get variable I make sure its a number.  Or usernames I always make sure theyre a-b A-B or _...