mysql_real_escape_string

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

mysql_real_escape_string

Post 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.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: mysql_real_escape_string

Post 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);
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post 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'");
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: mysql_real_escape_string

Post 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'];
          }

nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post 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?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: mysql_real_escape_string

Post 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'");
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: mysql_real_escape_string

Post 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?
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post 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.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: mysql_real_escape_string

Post 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.
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post by nitediver »

It's for user input, I have to sanitize it.
-Login
-Search

Ok, thanks.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: mysql_real_escape_string

Post 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?)
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post by nitediver »

Now how to test whether it work or not, for preventing sql injection?
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: mysql_real_escape_string

Post 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.
nitediver
Forum Contributor
Posts: 109
Joined: Tue Feb 24, 2009 9:05 am

Re: mysql_real_escape_string

Post by nitediver »

Thanks, Ill try that.
Post Reply