-1

I am using a particular query for inserting records. It is going well. I am even fetching records with a select query. But my problem is that, if the record contains single quotes ' ', then it gives me this error:

> NOTE:You have an error in your SQL syntax; 
>     check the manual that corresponds to your MySQL server 
>     version for the right syntax to use near 'B''' at line 1

The SELECT query is:

$result= mysql_query("SELECT s.name, 
                             s.sid as sid, 
                             s.category, 
                             p.name as pname 
                         FROM poet p 
                         INNER JOIN song s 
                            ON p.pid = s.pid 
                         WHERE s.name= '$sid'") or die(mysql_error());

What should I do to skip quotes problem in this. When I want quotes to insert in my records.

Geoffrey
  • 5,407
  • 10
  • 43
  • 78
Aditii
  • 353
  • 1
  • 5
  • 15
  • 3
    See http://stackoverflow.com/questions/2665744/problem-storing-string-containing-quotes – JJJ Aug 15 '11 at 07:38
  • Instead of inserting quotes into your records, you could rather place quotes around the text when you extract it from the database and display it. – Geoffrey Aug 15 '11 at 07:54

3 Answers3

4

Your problem is much worse than this -- what if someone enters the value '; DROP TABLE poet; --? You need to use either mysql_real_escape_string() to escape the value, or use parametrized queries (with PDO, for example).

It's 2011, for crying out loud. Why is SQL injection still a widespread problem?

cdhowie
  • 158,093
  • 24
  • 286
  • 300
  • Well... PHP's mysql extension has automatic protection from multiple queries but your point is still *very* valid – Phil Aug 15 '11 at 07:45
  • 1
    Yes, I intentionally don't bring that up because hopefully the example will scare the crap out of the developer and get them to pay attention. ;) – cdhowie Aug 15 '11 at 07:47
  • There's plenty of other nasty things you can do in a single query. My favourite is injecting some `UNION` clauses – Phil Aug 15 '11 at 07:50
  • Indeed. Unfortunately I lack the mental energy to come up with any at 4 AM, but feel free to add some here and I might add them to my answer (with credit, of course). – cdhowie Aug 15 '11 at 07:53
  • 2
    maybe: $sid = "' AND 1 = 2 UNION SELECT host, user, password, Grant_priv FROM mysql.user WHERE '' = '"; – Puggan Se Aug 15 '11 at 08:38
1

You have to escape the data

$sid = mysql_real_escape_string($sid);
Ragnar123
  • 5,174
  • 4
  • 24
  • 34
0

use http://www.php.net/manual/en/function.mysql-real-escape-string.php function on your string to quote "'" and other special symbols Other way to prevent injection - use different connections (login-passwords) with different rights for inserting and selecting. In this case mysql_real_escape_string wi9ll work good

Greenisha
  • 1,417
  • 10
  • 14
  • 2
    This one is deprecated and does not solve mysql injection. Have a look at the answer @cdhowie gave. – HTBR Aug 15 '11 at 07:41
  • 1
    `mysql_real_escape_string` doesn't either, under a very specific set of circumstances. ;) – cdhowie Aug 15 '11 at 07:43