Page 1 of 1
mysql_real_escape_string
Posted: Tue Jun 22, 2010 10:06 am
by nitediver
I am just start using mysql_real_escape_string, I try to use it in this login code.
But I received error, although I already follow example in the manual.
What does "%s" used for, doesn't explain on manual?
[text]Parse error: syntax error, unexpected T_STRING in ...\esc.php on line 11[/text]
Code: Select all
<?
$username=$_POST['username'];
$password=md5($_POST['password']);
$result = mysql_query("SELECT * FROM user WHERE username='%s' AND password='%s'"
mysql_real_escape_string($username),
mysql_real_escape_string($password));
if(mysql_num_rows($result)!='0'){
$_SESSION['username'] = $username;
header("location:".$ref);
exit;
}else{
header ("location:index.php?login=fail");
}
?>
Thanks.
Re: mysql_real_escape_string
Posted: Tue Jun 22, 2010 11:56 am
by Jade
You need to go back to the PHP manual... this is not the same code that's there:
http://php.net/manual/en/function.mysql ... string.php, you've removed the sprintf function.
The %s is used with sprintf to denote that the items following the query should be substituted into the rest of the string once the function call has completed.
Essentially this:
Code: Select all
$sql = sprintf("SELECT * FROM user WHERE username='%s' AND password='%s'", mysql_real_escape_string($username), mysql_real_escape_string($password));
Becomes this:
Code: Select all
$sql = sprintf("SELECT * FROM user WHERE username='" . mysql_real_escape_string($username) . "' AND password='" . mysql_real_escape_string($password) . "'");
Then you can run the query on your $sql string.
Code: Select all
$sql = sprintf("SELECT * FROM user WHERE username='" . mysql_real_escape_string($username) . "' AND password='" . mysql_real_escape_string($password) . "'");
$result = mysql_query($sql);
Re: mysql_real_escape_string
Posted: Wed Jun 23, 2010 9:31 am
by nitediver
Now using "sprintf" not "msyql_query" anymore?
This is my recent code, is this right?
Code: Select all
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string(md5($_POST['password']));
$result = mysql_query("SELECT * FROM user WHERE username='$username' AND password='$password'");
Re: mysql_real_escape_string
Posted: Wed Jun 23, 2010 11:40 am
by Jade
I would change this:
Code: Select all
$password=mysql_real_escape_string(md5($_POST['password']));
To this:
Code: Select all
$password=md5(mysql_real_escape_string($_POST['password']));
Then you can retrieve the data from your query result like this:
Code: Select all
$result = mysql_query("SELECT * FROM user WHERE username='$username' AND password='$password'");
while ($row = mysql_fetch_array($result))
{
//loop through your results
//print the entire array of results or
print_r($row) . "<br/>";
//print a specific row value
echo $row['table_row_name_here'];
}
Re: mysql_real_escape_string
Posted: Sun Jun 27, 2010 10:26 am
by nitediver
Ok,
1. But how to escape character which is not escaped by mysql_real_escape_string ?
2. Same question but for htmlspecialchars.
Currently I am using conditional if, is it the best way?
Re: mysql_real_escape_string
Posted: Sun Jun 27, 2010 10:40 am
by Apollo
Jade wrote:I would change this:
Code: Select all
$password=mysql_real_escape_string(md5($_POST['password']));
To this:
Code: Select all
$password=md5(mysql_real_escape_string($_POST['password']));
This is wrong! You are now taking the md5 hash of the escaped password, not the original. Besides the fact that mysql_real_escape_string might differ from one server to another (depending on the exact SQL server which it relates to), you should always use escaping as the last step. Otherwise, subsequent steps might add new characters that require escaping.
However, when using md5 (which, by the way, is ideally replaced with sha512 + usage of a salt string) you are only getting hexadecimal output, hence there's no need to escape anything at all. It's perfectly safe to do:
Code: Select all
$username = mysql_real_escape_string($_POST['username']);
$password = md5($_POST['password']);
$result = mysql_query("SELECT * FROM user WHERE username='$username' AND password='$password'");
Re: mysql_real_escape_string
Posted: Sun Jun 27, 2010 10:41 am
by Apollo
nitediver wrote:Ok,
1. But how to escape character which is not escaped by mysql_real_escape_string ?
Why would you want to escape them?
Re: mysql_real_escape_string
Posted: Sun Jun 27, 2010 10:51 am
by nitediver
Sorry I change my question, I thought both htmlspecialchars & mysql_real_escape_string are working the same way.
I change my question:
How to escape character which is not escaped by htmlspecialchars, does using if is the best way ?
Thanks.
Re: mysql_real_escape_string
Posted: Sun Jun 27, 2010 12:26 pm
by Apollo
Exactly why do you want to escape strings?
If it is to output (print/echo) from PHP to generate HTML, use htmlspecialchars.
If it is to use in SQL queries, use mysql_real_escape_string.
Re: mysql_real_escape_string
Posted: Mon Jun 28, 2010 5:09 am
by nitediver
It's for user input, I have to sanitize it.
-Login
-Search
Ok, thanks.
Re: mysql_real_escape_string
Posted: Mon Jun 28, 2010 7:06 am
by Apollo
nitediver wrote:It's for user input, I have to sanitize it.
-Login
-Search
Assuming you need the user input string in an SQL query for either purpose (to search/verify a user account, or to search for content, respectively), then again: mysql_real_escape_string is all you need. It escapes the characters that requires escaping, and leaves other characters alone (which is what you want, because why would you ever want to escape other characters than necessary).
(Or were you perhaps mistakenly thinking of
stripping rather than escaping?)
Re: mysql_real_escape_string
Posted: Sat Jul 03, 2010 9:06 am
by nitediver
Now how to test whether it work or not, for preventing sql injection?
Re: mysql_real_escape_string
Posted: Tue Jul 06, 2010 9:02 am
by Jade
There are several nice addon's for firefox you can use to test for SQL injections:
https://addons.mozilla.org/en-US/firefo ... on&cat=all
Or you can always try to do it yourself by entering something like ' OR 1=1; into a search form that you expect to only return a limited number of results.
Re: mysql_real_escape_string
Posted: Thu Jul 08, 2010 1:29 am
by nitediver
Thanks, Ill try that.