Sql Query detecting apostrophes

Question

I'm trying to execute a Mysql_Query, which uses an input from a Textfield POST request. Though, I am encountering the follow error if the POST request includes an apostrophe.

"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 ''DJ Broski, DJ CJ and Wendy; Looking Chill'')' at line 1"

How am I able to resolve this? The code I use to execute the query;

            $sql = "INSERT into `gallery_pictures` (`fileName`,`caption`) VALUES ('0','$photo_caption')";
        $result = @mysql_query($sql);

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use [a modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to** [**SQL injection attacks**](http://bobby-tables.com/) that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — DCoder, Sep 03 '13 at 13:46
First off use mysqli functions and secondly look into http://www.php.net/manual/en/mysqli.real-escape-string.php — , Sep 03 '13 at 13:47
Please provide the result of: echo $sql; It would probably help if you DIDN'T suppress errors!! — Strawberry, Sep 03 '13 at 13:47
this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php ( having old style mysql queries, pdo and so on...) long story short: use parameterized queries ( and maybe additional string santitysation ) — Najzero, Sep 03 '13 at 13:47
This is yet another reason to use parametrized queries. If you use parametrized queries, you don't have to worry about the apostrophes or SQL injection. — Andy Lester, Sep 03 '13 at 14:18

Paolo Gibellini · Answer 1 · 2013-09-04T20:37:23.563

0

I agree with @DCoder about the needs of a modern replacement.

As written in the other posts, in PHP there is a built-in function to be used for these purposes, but is better using parameterized queries.

edited Sep 04 '13 at 20:37

answered Sep 03 '13 at 13:53

Paolo Gibellini

310
13
21

3

It's better to use the in-built functions to do this, rather than trying to create your own: http://www.php.net/manual/en/function.mysql-real-escape-string.php – andrewsi Sep 03 '13 at 13:57
Yes, the code was written as an example: built-in functions are useful (and charset-problems-aware), but is a good thing understand /how/ they act. – Paolo Gibellini Sep 03 '13 at 14:06
Thank you, @andrewsi, I've updated my post in a less ambiguos form – Paolo Gibellini Sep 03 '13 at 14:16
i'd upvote you if you used PDO instead of mysqli_ because PDO is all-round better than mysqli_ – STT LCU Sep 03 '13 at 14:16
Escaping is only *roughly* similar to that. There are other things going on which make it impractical to write your own escaping function. Also, don't use the `mysqli_real_escape_string` function. Parameterized queries eliminate the need for this. If this is how you use `mysqli`, you're doing it wrong. – tadman Sep 03 '13 at 15:53
@tadman: If you read `carefully` my post, you can find that I've written the escaping function (as you define it) as an example. – Paolo Gibellini Sep 04 '13 at 07:44
@PaoloGibellini If you read my comment *carefully* you'd also notice that my big grievance is you're not using parameterized queries. This is a massive oversight. String interpolation of values that may or may not be escaped based on the discipline of the programmer is just too risky, one mistake away from failure. Parameterized queries are much harder to get wrong, several things have to slip. – tadman Sep 04 '13 at 17:57
@tadman: my post begin with an accent to the need of a modern replacement, referring to a note with a lot of suggestions. My idea was explaining with an example how avoid the SQL syntax error using escaping. I apologize for not being in my intent (mainly due to my coarse ability of writing fast in English). I've corrected my post because it'd seemed to invite to write own escaping functions. – Paolo Gibellini Sep 04 '13 at 20:35

score -1 · Answer 2 · answered Sep 04 '13 at 20:48

-1

$sql = "INSERT into `gallery_pictures` (`fileName`,`caption`) VALUES ('0',\"$photo_caption\")";
        $result = @mysql_query($sql);

how about this?

answered Sep 04 '13 at 20:48

mint

69
4

No, that doesn't change anything. This is a lack of escaping that's causing the problems. Switching to a different kind of quotation marks only obscures the true problem. **Escape properly**. Always. No exceptions. – tadman Sep 05 '13 at 00:53

Sql Query detecting apostrophes

2 Answers2