mysql_query error with single quotes in php

Question

Possible Duplicate:
How do I handle single quotes inside a SQL query in PHP?

I had written the following code to fetch a data from a mysql table:

$clg=$row['text'];
$query1 = "SELECT * FROM user WHERE text='$clg'";
$result1 = mysql_query($query1,$con) or die(mysql_error());
$count=mysql_num_rows($result1);
echo $count;

But the text field has a single quote(') which closes the single quotes in $query1, hence resulting in mysql syntax error. How can I rectify this?

Use [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. — eggyal, Sep 09 '12 at 18:56
this is quite surprising to see that so few people are flagging this question as duplicate...the same question is asked at least once a week! — Jocelyn, Sep 09 '12 at 19:21
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the [*red box*](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — tereško, Sep 09 '12 at 19:31

score 1 · Accepted Answer · answered Sep 09 '12 at 19:19

1

$clg=$row['text'];
$query1 = "SELECT * FROM user WHERE text='" . mysql_real_escape_string($clg) . "'";
$result1 = mysql_query($query1,$con) or die(mysql_error());
$count=mysql_num_rows($result1);
echo $count;

But you should know that mysql_* functions family will be deprecated soon. Please read the red box here located on php.net website.

answered Sep 09 '12 at 19:19

Alain Tiemblo

36,099
17
121
153

1

I will spare the -1 for writing new code with `mysql_` functions since you did disclaimer it, BUT.... why write the code at all? Don't use `mysql_*` in your answers and you don't have to add the disclaimer. The only way to get beginners to stop using these functions is to stop giving beginners answers that utilize them. If you advocate PDO, answer with PDO! – Chris Baker Sep 11 '12 at 20:36
I understand your point. I didn't wrote that code, I took it from the question and corrected the mistake so that the questionner could understand what's wrong. It should be a shame if my answer ask him 3 more new questions and doesn't answer his's, shouldn't be? I understand your point anyway. – Alain Tiemblo Sep 11 '12 at 21:59

score -1 · Answer 2 · edited Sep 10 '12 at 01:48

-1

<?php
function escape($string) {
    if(get_magic_quotes_gpc()) $string = stripslashes($string);
    return mysql_real_escape_string($string);
}

write this function and call it

escape($clg);

for prevent every mysql syntax error and sql injection.`

edited Sep 10 '12 at 01:48

Lusitanian

11,012
1
41
38

answered Sep 09 '12 at 19:10

Ziaullah

65
2
6

As of PHP 5.4, the magic quotes feature was removed from PHP. – Jocelyn Sep 09 '12 at 19:11
If an older version of PHP is being used (e.g. 5.2), it is better to turn off magic quotes in the configuration. – uınbɐɥs Sep 11 '12 at 20:27
`mysql_*` functions are slated to be deprecated. Do not use them in new code, instead use `mysqli_*` or PDO. Also, as noted, magic quotes are gone in modern PHP versions. -1 – Chris Baker Sep 11 '12 at 20:30

mysql_query error with single quotes in php

2 Answers2