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

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

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
.
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 ...
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 ...
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
