-5

I have a weird problem.

I have a table which has a title field.
I am inserting values into this title field using mysql_real_escape_string. Inserting is working fine for values with single quotes.
Some other place I am doing a select using title filed in the where clause as below

SELECT * FROM table WHERE title=mysql_real_escape_string(Girish's Photo);

This query is returning empty result set even when I inserted Girish's Photo.

---- Editing to put some code

$photo_title=mysql_real_escape_string($_POST[photo_title]);<br/>
$sql = "INSERT INTO photos values($id,'$photo_title');<br/>

using this from a form I have inserted Girish's Photo into photo_title. It worked fine.
... .. ..

Then at some other place in PHP

$title="Girish's Photo";
$sql = "SELECT photo_id,photo_title FROM photos WHERE photo_title ='" . mysql_real_escape_string($title)."'" ;

But this query is returning empty result set.

Using phpMyAdmin, if I try to run the above query .. the result is empty. If I browse the table I see value Girish\'s Photo
Now if I run the query on phpMyAdmin replacing where clause with where photo_title='Girish\''s Photo' I am getting the record.

deceze
  • 510,633
  • 85
  • 743
  • 889
Girish
  • 21
  • 3
  • 5
    mysql_real_escape_string is a php function – Dr. Dan Dec 11 '12 at 11:33
  • 3
    Your problem is wired? That is really weird. – Alvin Wong Dec 11 '12 at 11:36
  • As above. To use it in a php script you would have something like $query = "select * from table where title='".mysql_real_escape_string("Girish's Photo")."'"; – Kickstart Dec 11 '12 at 11:37
  • 2
    Show some real code, what you show should shouldn't work. – deceze Dec 11 '12 at 11:37
  • 6
    OT: **Use of `mysql_*` extension is discouraged.** Instead, the [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://php.net/manual/en/ref.pdo-mysql.php) extension should be used. And you should really use [prepared queries](http://php.net/manual/en/mysqli.prepare.php) instead of simply escaping. – Alvin Wong Dec 11 '12 at 11:37

5 Answers5

3
  $data = "Girish's Photo";

  $query = "SELECT * FROM table WHERE title='".mysql_real_escape_string($data)."'";
Daya
  • 1,170
  • 10
  • 22
1

mysql_real_escape_string() is a PHP-function, which should be used as follow:

"SELECT * FROM table WHERE title='".mysql_real_escape_string("Girish's Photo")."'";

However, this is bad practice.

looper
  • 1,929
  • 23
  • 42
0

I had a similar problem recently which I solved by using htmlentites() instead of mysql_real_escape_string() check it out in the manual or w3 schools

EDIT: this is a valid answer because he's using mysql_real_escape_string() in the wrong context in the first place. if you read the question, he's escaping a FILENAME and therefore he's not at risk of injection. If you're going to downvote at least say why..

I wrestled a bear once.
  • 22,983
  • 19
  • 69
  • 116
  • It doesn't matter *what* he's escaping, it matters what context he's escaping for. For SQL context, you always escape using SQL functions. See http://kunststube.net/escapism. – deceze Dec 11 '12 at 12:40
0

Okay so you're going to want to use PDO for all queries. Primarily for the following reasons:

  1. mysql_* is being deprecated.
  2. It's not safe from SQL Injection.
  3. PDO is capable of accessing numerous database engines making it much more flexible without changing the API.

Please take a look at this post to get a look at how to issue a SELECT using PDO.

Parameterized SELECT queries via PDO?

Community
  • 1
  • 1
Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • 1
    PDO isn't safe from SQL injection either **if used improperly**. The mysql_ API is perfectly safe from SQL injection **if used properly**. – deceze Dec 11 '12 at 12:06
  • he's not at risk of injection, it's a filename which already exists in the database, he's just having a problem with the quote. – I wrestled a bear once. Dec 11 '12 at 12:39
  • @deceze, can you **edit** my answer and show me how you could use the *mysql_* API to prevent SQL Injection? Further, wouldn't point number one essentially be the most prominent reason not to use that API? – Mike Perrenoud Dec 11 '12 at 12:39
  • How to use the mysql_ API to prevent injection...? By applying `mysql_real_escape_string` correctly, that's what it's for. I don't understand the question. – deceze Dec 11 '12 at 12:41
  • @deceze, I think what I'm saying is the only way I've ever known to ensure that SQL Injection cannot occur is to parameterize the statement. However, bear in mind I come from 12+ years of .NET programming. – Mike Perrenoud Dec 11 '12 at 13:33
  • @Michael Then you as well should read http://kunststube.net/escapism. Note that parameterized statements *are* better, since they're easier to use and have additional functionality, and that you *shouldn't* use the mysql_ API since it's deprecated. But saying that it's inherently vulnerable to SQL injection is wrong. – deceze Dec 11 '12 at 13:41
0

The value in your database should not contain backslashes. That's why your query doesn't match. Girish's Photo does not match Girish\'s Photo. Sounds like you are a victim of magic quotes. Read the manual and get rid of them.

deceze
  • 510,633
  • 85
  • 743
  • 889