How to use mysql_real_escape_string function in PHP

Question

So in this program I'm writing, I actually grab a SQL query from the user using a form. I then go on to run that query on my database.

I know not to "trust" user input, so I want to do sanitization on the input. I'm trying to use mysql_real_escape_string but have been unsuccessful in getting it to work.

Here's what I'm trying, given the input: select * from Actor;

//"query" is the input string: 
$clean_string = mysql_real_escape_string($query, $db_connection); 
$rs = mysql_query($clean_string, $db_connection); 
if (!$rs) 
{ 
    echo "Invalid input!"; 
}

This is ALWAYS giving me the

"Invalid input!"

error.

When I take out the clean_string part and just run mysql_query on query, the

"invalid input"

message is not output. Rather, when I do this:

$rs = mysql_query($query, $db_connection); 
if (!$rs) 
{ 
   echo "Invalid input!"; 
}

It does NOT output

"invalid input".

However, I need to use the mysql_real_escape_string function. What am I doing wrong?

Update:

Given select * from Actor; as an input, I've found the following.

Using echo statements I've found that before sanitizing, the string holds the value: select * from Actor; which is correct. However, after sanitizing it holds the incorrect value of select *\r\nfrom Actor;, hence the error message. Why is mysql_real_escape_string doing this?

I dont understand entering part... Are you grabbing entire query from user input or just some data to use later to generate query? I hope, that's not first case or you have serious problem with security. — Wh1T3h4Ck5
– Wh1T3h4Ck5, Commented Apr 17, 2011 at 4:32
Chang: echo "Invalid Input"; To this: die('Invalid Input: ' . mysql_error()); To see if you get a mysql error. — k to the z
– k to the z, Commented Apr 17, 2011 at 4:35
You might consider the option of using the mysqli interface with parameterized queries and avoiding the whole issue. It's what mysql recommends. — dkretz
– dkretz, Commented Apr 17, 2011 at 4:37
Wh1T3h4Ck5: I probably should have specified that this is just a small project for a course I'm taking. It's true that I'm grabbing as input the entire query. Don't worry, I'm not asking you guys to do my homework. This error is obviously a minor part of my actual project. — Casey Patton
– Casey Patton, Commented Apr 17, 2011 at 4:43

CrayonViolent · Accepted Answer · 2011-04-17 04:32:34Z

13

use it on the actual values in your query, not the whole query string itself.

example:

$username = mysql_real_escape_string($_POST['username']);
$query = "update table set username='$username' ...";
$rs = mysql_query($query);

answered Apr 17, 2011 at 4:32

CrayonViolent

32.6k6 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

outis · Accepted Answer · 2011-04-17 04:37:35Z

1

Rather than using the outdated mysql extension, switch to PDO. Prepared statement parameters aren't vulnerable to injection because they keep values separate from statements. Prepared statements and PDO have other advantages, including performance, ease of use and additional features. If you need a tutorial, try "Writing MySQL Scripts with PHP and PDO".

answered Apr 17, 2011 at 4:37

outis

77.7k23 gold badges155 silver badges228 bronze badges

Comments

mario · Accepted Answer · 2011-04-17 04:56:42Z

1

mysql_real_escape_string() is the string escaping function. It does not make any input safe, just string values, not for use with LIKE clauses, and integers need to be handled differently still.

An easier and more universal example might be:

 $post = array_map("mysql_real_escape_string", $_POST);
 // cleans all input variables at once

 mysql_query("SELECT * FROM tbl WHERE id='$post[id]' 
                OR name='$post[name]' OR mtime<'$post[mtime]' ");
 // uses escaped $post rather than the raw $_POST variables

Note how each variable must still be enclosed by ' single quotes for SQL strings. (Otherwise the escaping would be pointless.)

edited Apr 17, 2011 at 4:56

answered Apr 17, 2011 at 4:39

mario

146k20 gold badges243 silver badges293 bronze badges

Comments

Zach Rattner · Accepted Answer · 2011-04-17 04:34:19Z

0

You should use mysql_real_escape_string to escape the parameters to the query, not the entire query itself.

For example, let's say you have two variables you received from a form. Then, your code would look like this:

$Query = sprintf(
    'INSERT INTO SomeTable VALUES("%s", "%s")', 
    mysql_real_escape_string($_POST['a'], $DBConnection),
    mysql_real_escape_string($_POST['b'], $DBConnection)
);

$Result = mysql_query($Query, $DBConnection);

answered Apr 17, 2011 at 4:34

Zach Rattner

21.5k10 gold badges63 silver badges84 bronze badges

Comments

Wh1T3h4Ck5 · Accepted Answer · 2011-04-17 04:39:44Z

0

manual mysql_real_escape_string()

Escapes special characters in a string for use in an SQL statement

So you can't escape entire query, just data... because it will escape all unsafe characters like quotes (valid parts of query).

If you try something like that (to escape entire query)

echo mysql_real_escape_string("INSERT INTO some_table VALUES ('xyz', 'abc', '123');");

Output is

INSERT INTO some_table VALUES (\'xyz\', \'abc\', \'123\');

and that is not valid query any more.

edited Apr 17, 2011 at 4:39

answered Apr 17, 2011 at 4:34

Wh1T3h4Ck5

8,52910 gold badges63 silver badges82 bronze badges

Comments

dwolf · Accepted Answer · 2015-07-18 20:41:45Z

0

This worked for me. dwolf (wtec.co)

<?php
// add data to db
require_once('../admin/connect.php');

$mysqli = new mysqli($servername, $username, $password, $dbname);

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$post = $mysqli->real_escape_string($_POST['name']);
$title = $mysqli->real_escape_string($_POST['message']);


/* this query with escaped $post,$title will work */
if ($mysqli->query("INSERT into press (title, post) VALUES ('$post', '$title')")) {
    printf("%d Row inserted.\n", $mysqli->affected_rows);
}

$mysqli->close();


//header("location:../admin"); 
?>

edited Jul 18, 2015 at 20:41

answered Jul 18, 2015 at 19:14

dwolf

12 bronze badges

1 Comment

TomTerrific Over a year ago

you swapped the elements in VALUES()

Collectives™ on Stack Overflow

How to use mysql_real_escape_string function in PHP

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related