Apostrophe in php query doesn't work

Question

I have a table that contains name of movies and other things and in my php page I want to select some of that movies..

$sql3 = "SELECT * FROM Movies where Movies.nameM='{$row['nameM']}';";

This query crashes when the nameM (name of movie) has an apostrophe in its name :/

How can I change this to work well?

Thanks for your help

If you're having this problem, you should have a very, very good look at http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Jeremy Smyth, Dec 18 '13 at 17:22
I downvoted because I feel it lacks research effort. Nothing personal. — Tim Seguine, Dec 18 '13 at 17:27
Which MySQL functions are you using? If you use prepared statements, this issue would be a thing of the past :) — gen_Eric, Dec 18 '13 at 17:28
(1) Google for "SQL injection". Read and discover why you need to be really *really* careful when building DB strings. (2) Start using the PDO library for your PHP DB code, and use its "Prepared Statement" functionality to get around this issue. (3) Find a few *good quality* PHP tutorials (eg http://www.phptherightway.com/#databases). — Spudley, Dec 18 '13 at 17:29

score 4 · Answer 1 · edited Dec 18 '13 at 17:42

4

You may try:

$sql3 = "SELECT * FROM Movies where Movies.nameM='".mysqli_real_escape_string($row['nameM'])."';";

And if you are using the old and deprecated mysql_* functions:

$sql3 = "SELECT * FROM Movies where Movies.nameM='".mysql_real_escape_string($row['nameM'])."';";

edited Dec 18 '13 at 17:42

Funk Forty Niner

74,450
15
68
141

answered Dec 18 '13 at 17:24

MillaresRoo

3,808
1
31
37

this is by far the most appropriate answer. might you also add an example using `mysql_real_escape_string()` for backward compatibility purposes? – Joshua Burns Dec 18 '13 at 17:26
4

MySQL_* functions are now deprecated, so shouldn't be used. – Connor Gurney Dec 18 '13 at 17:28

score 0 · Answer 2 · answered Dec 18 '13 at 17:55

As Spudley said you should R&D more. Just to make it a few easier for you

You may use mysql(i)_ real escape functions but if you're using a framework works on top of PDO you don't have a mysql(i)_* connection and can't escape strings using them i have seen this problem previously on yii or f3 or ... Mostly the frameworks support the param safe injection.

\Framework::Query("SELECT .... WHERE column=:param", array(':param'=>$value));

But some times you may need to escape the string value manually. To do that with PDO you can acquire the pdo object and use the :

substr($pdo->quote($string, , \PDO::PARAM_STR), 1, -1)

The only note is that the ->quote() also puts the apostrophes around the result which they can get wiped using substr.

Edited to make the codes clear

Apostrophe in php query doesn't work

2 Answers2