Page 1 of 1

sql injection prevention

Posted: Wed Jul 18, 2007 6:06 pm
by krraleigh
I can't remember where but someone read my code and told me that I needed to prevent sql injection. I posted back to asking how to prevent sql injection but never received a response back.

Can anyone advise me on what this is about and how to protect myself?
Or a link to this topic?

I googled but found nothing

Kevin Raleigh

Re: sql injection prevention

Posted: Wed Jul 18, 2007 6:10 pm
by John Cartwright
krraleigh wrote:I googled but found nothing
I seriously doubt that 8O Googling SQL injection or more specifically php sql injection yields many excellent sources on the matter

to answer your question, mysql_real_escape_string() is the function you want

sql injection

Posted: Wed Jul 18, 2007 6:21 pm
by krraleigh
I appreciate the quick response

Thank You
Kevin Raleigh

Posted: Wed Jul 18, 2007 7:11 pm
by nathanr
remember to check magic quotes :)

sql injection

Posted: Wed Jul 18, 2007 7:12 pm
by krraleigh
Thank You
I most certainly will

Kevin Raleigh

Re: sql injection prevention

Posted: Wed Jul 18, 2007 7:40 pm
by webgroundz
Jcart wrote:
krraleigh wrote:I googled but found nothing
I seriously doubt that 8O Googling SQL injection or more specifically php sql injection yields many excellent sources on the matter

to answer your question, mysql_real_escape_string() is the function you want
Jcart is correct, here is my sample function for magic_quotes

Code: Select all

function quote($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;
	}
hope this one helps.

$name = 'hello world';
quote($name);
:) :) :)

Posted: Thu Jul 19, 2007 6:14 pm
by krraleigh
Does this look like it will hold up for sql injection:

Code: Select all

$insert = sprintf("INSERT INTO user (username, password, fName, lName, passHint, email, bMonth, secureID)
	VALUES ('%s', '%s', '%s', '%s', '%d', '%d')",
		quote($_POST['username']),
		quote($_POST['pass']),
		quote($_POST['fName']),
		quote($_POST['lName']),
		quote($_POST['passHint']),
		quote($_POST['email']),
		$_POST['bMonth'],
		$secureID);
		$add_member = mysql_query($insert);
			
function quote($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; 
        }
The reason I ask is I noticed that mysql_real_escape_string($value) actually should have two values according to http://us.php.net/mysql_real_escape_string
It should look like this mysql_real_escape_string($value, $link)

$link = mysql_connect("localhost", "root", "") or die(mysql_error());


insight always appreciated
thank you
Kevin Raleigh

sql

Posted: Thu Jul 19, 2007 7:08 pm
by krraleigh
I thought something didn't look right and it was $secureID.
Everything works as it should but for some reason when I try to add the variable $secureID,
it won't load the contents. I echoed the contents and the value is there it just won't load
into the database.

any ideas?

Thank You
Kevin Raleigh

Code: Select all

$insert = sprintf("INSERT INTO user (username, password, fName, lName, passHint, email, secureID, bMonth)
 VALUES ('%s',  '%s',   '%s',  '%s',    '%s',   '%s',    '%d',   '%d')",
mysql_real_escape_string($userName, $link),		
mysql_real_escape_string($myPass, $link),
mysql_real_escape_string($myFName, $link),
mysql_real_escape_string($myLName, $link),
mysql_real_escape_string($myHint, $link),
mysql_real_escape_string($myEmail, $link),
$secureID,
$_POST['bMonth']);
$add_member = mysql_query($insert, $link);

Posted: Thu Jul 19, 2007 7:16 pm
by webgroundz
krraleigh wrote:Does this look like it will hold up for sql injection:

Code: Select all

$insert = sprintf("INSERT INTO user (username, password, fName, lName, passHint, email, bMonth, secureID)
	VALUES ('%s', '%s', '%s', '%s', '%d', '%d')",
		quote($_POST['username']),
		quote($_POST['pass']),
		quote($_POST['fName']),
		quote($_POST['lName']),
		quote($_POST['passHint']),
		quote($_POST['email']),
		$_POST['bMonth'],
		$secureID);
		$add_member = mysql_query($insert);
			
function quote($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; 
        }
The reason I ask is I noticed that mysql_real_escape_string($value) actually should have two values according to http://us.php.net/mysql_real_escape_string
It should look like this mysql_real_escape_string($value, $link)

$link = mysql_connect("localhost", "root", "") or die(mysql_error());


insight always appreciated
thank you
Kevin Raleigh
link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

yeah youre right it has two parameters, but as the documentation of php said : If the link identifier is not specified, the last link opened by mysql_connect() is assumed

so if ever you have

Code: Select all

$link = mysql_connect("localhost", "root", "") or die(mysql_error());
in your connection you don't need to put the parameter of the link identifier always when you use

Code: Select all

mysql_escape_string()
.


thanks... :) :) :)

login problems

Posted: Thu Jul 19, 2007 8:25 pm
by krraleigh
I took care of my sql injection problem but all my strings are quoted as you know.

So how do I prep my username so that I can compare it to the db?

Code: Select all

// makes sure they filled it in
  if(!$_POST['username'] || !$_POST['pass']) {
  die('You did not fill in a required field.');
  }
  
 // checks it against the database

$check = mysql_query("SELECT * FROM user WHERE username = '".$_POST['username']."'")or die(mysql_error());
I tried addslashes();
and I tried to use the mysql_real_escape_string($userName)
but ... :lol:
insight appreciated
thank you
kevin

Re: login problems

Posted: Thu Jul 19, 2007 8:45 pm
by John Cartwright
krraleigh wrote: I tried addslashes();
and I tried to use the mysql_real_escape_string($userName)
but ... :lol:
kevin
Don't use addslashes(), all you need to use is mysql_real_escape_string()..

reading username

Posted: Thu Jul 19, 2007 9:03 pm
by krraleigh
I tried doing that and my error message is kicked out.

Code: Select all

$myUserName = mysql_real_escape_string($_POST['username']);
	
$check = mysql_query("SELECT * FROM user WHERE username = '$myUserName'")or die(mysql_error());
	
  //Gives error if user dosen't exist
  $check2 = mysql_num_rows($check);
  if (empty($check2)) {
  die("Your password or username is not in the Db, Please use the back button and try again");
  }
Can you advise further

Kevin Raleigh

sql injection

Posted: Thu Jul 19, 2007 10:08 pm
by krraleigh
I found a way around the problem
When I echo my variable before using it in the select * statement
it shows: 'username' but when I use it in the select statement it won't work

So what I had to do to make it work was:
$check = mysql_query("SELECT * FROM user WHERE username = \"$myUserName\"")or die(mysql_error());

Notice the use of the quotes around the variable and the slashes protecting the quotes.
I tried using ' ' single quotes but that produced a sql error because the output that the server
saw was 'username'''

My best quess is that the variable's quotes are absorbing the ' ' that encompass the value in the DB so that I am comparing the following:

username = 'username'

the second variable is from the database

I spent several hours on this and found an article here showing this function:
http://www.w3schools.com/php/func_mysql ... string.asp

Code: Select all

The correct way to do it to prevent database attack:

<?php
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}$con = mysql_connect("localhost", "peter", "abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }// Make a safe SQL
$user = check_input($_POST['user']);
$pwd = check_input($_POST['pwd']);
$sql = "SELECT * FROM users WHERE
user=$user AND password=$pwd";mysql_query($sql);mysql_close($con);
?>
However if you look at my code and compare it to theres you will see that I was forced to use the " " double quotes and they don't show that they are needed.

Would really be interested in any critiques on this.
Seems very difficult to query the data base after using mysql_real_escape_string($value)

insight always appreciated
thank you
Kevin :P