MYSQL: how do I insert a string that contains apostrophes?

[ocelot]

so I have a form where somebody can enter a big string with any characters they want in it. so sometimes the string contains one or more apostrophes.

well, that string, as while as a few others are submitted to a php script that insterts them into a few columns in the database.

so for my query I say

$query =
"INSERT INTO blog_posts (keywords, body, timestamp, picpost, related_posts)
VALUES ('"
. $keywords . "', '"
. $body . "', '"
. $timestamp . "', '"
. $picpost . "', '"
. $related_posts . "')"

as you can see, when it encounters an apostrophe, that causes it to break the code.

so how can I write it to accept apostrophes or any other characters?

[dmorison]

You need mysql_escape_string() [uk.php.net] :)

[PhraSEOlogy]

You can always use placeholders and you dont need to worry about escaping anything.

my $sth = $dbh->prepare("INSERT INTO testtable VALUES (?,?)");

$dbh->execute($val1, $val2);

[Salsa]

I'd think that simply

addslashes($body);

etc. before the INSERT would do the trick.

[Salsa]

Also, you don't need to do all of that concatination stuff in your query, simply

$query = "INSERT INTO blog_posts (keywords, body, timestamp, picpost, related_posts) VALUES('$keywords', '$body', '$timestamp', '$picpost', '$related_posts')";

will work fine, plus it'll be easier to type and read.

[dreamcatcher]

To increase SQL efficiency, its a good idea to convert the apostrophes to character entities using str_replace()

$body = str_replace("'", "'", $body);

This eleviates any problems with apostrophes. In some cases you may need to convert the character entity back.