PHP: Error when inserting quotation marks in mySQL

Question

I insert a text variable in a mySQL table. Everything works fine except in the text is a quotation mark. I thought that I can prevent an error by using "mysql_real_escape_string". But there is an error anyway.

My insert statement:

 $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";
 mysql_real_escape_string($insertimage);

The error message: MySQL Error: 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 '1413885955514','10')' at line 1

Please read [the warning](http://php.net/manual/en/function.mysql-real-escape-string.php) — Alexander, Oct 21 '14 at 10:18
mysql_real_escape_string is used for the single string values within the query, not for complete query. — Danijel, Oct 21 '14 at 10:19

score 2 · Answer 1 · edited May 23 '17 at 10:26

You need to escape data that you are putting into the SQL so that any special characters in it don't break the SQL.

You are escaping all the special characters in the final string of SQL; even those that you want to have special meaning.

If you want to use your current approach, you would do something like this:

 $filename = mysql_real_escape_string($filename);
 $text = mysql_real_escape_string($text);
 $timestamp = mysql_real_escape_string($timestamp);
 $countdown = mysql_real_escape_string($countdown);
 $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";

… but the PHP mysql_ extension is obsolete and you shouldn't use it.

Modern APIs, such as mysqli_ and PDO support prepared statements, which are a better way to handle user input. This answer covers that in more detail.

score 0 · Answer 2 · answered Oct 21 '14 at 10:18

mysql_real_escape_string($insertimage);

You will have to use this function to each variables before writing the query.

$filename = mysql_real_escape_string($filename);
$text = mysql_real_escape_string($text);
$timestamp = mysql_real_escape_string($timestamp);
$countdown = mysql_real_escape_string($countdown);

$insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";

score 0 · Answer 3 · answered Oct 21 '14 at 10:18

0

Try this ,

  $insertimage = sprintf("INSERT INTO image(filename,text,timestamp,countdown) VALUES    ('%s','%s','%s','%s')", mysql_real_escape_string($filename), mysql_real_escape_string($text),   $timestamp, $countdown);

Why, because your inputs vars must be escaped before using them in sql

then execute your sql.

answered Oct 21 '14 at 10:18

Anas

366
1
6

I'm not sure how having this code as a runnable snippet is helpful?! – Lightness Races in Orbit Oct 21 '14 at 10:20

score 0 · Answer 4 · answered Oct 21 '14 at 10:20

0

Escaping the entire query is not useful. In fact, right now, you are causing syntax errors by doing so.

You should be escaping the individual variables that you inject into it.

answered Oct 21 '14 at 10:20

Lightness Races in Orbit

378,754
76
643
1,055

score 0 · Accepted Answer · answered Oct 21 '14 at 10:21

The problem with your current code is that you have not correctly escaped the values you're trying to enter into the table.

Better still is to avoid the mysql_* function family entirely. Those functions are now deprecated and bring security risks to the table (along with other concerns).

You'd be better to use PDO and Prepared Statements, for example:

$db = new PDO('param1', 'param2', 'param3');
$sql = $db->prepare( 'INSERT INTO `image` (`filename`, `text`, `timestamp`, `countdown`) 
                                   VALUES (:filename, :text, :timestamp, :countdown)' );

$sql->execute( array(':filename' => $filename, 
                     ':text' => $text, 
                     ':timestamp' => $timestamp, 
                     ':countdown' => $countdown )
);

While true, you did not explain the actual problem with the OP's code. — Lightness Races in Orbit, Oct 21 '14 at 10:21
@LightnessRacesinOrbit I was in the process of editing the answer when you commented. — BenM, Oct 21 '14 at 10:23

score 0 · Answer 6 · answered Oct 21 '14 at 10:21

Try this:

$filename = mysql_real_escape_string($filename);
$text = mysql_real_escape_string($text);
$timestamp = mysql_real_escape_string($timestamp);
$countdown = mysql_real_escape_string($countdown);

$insertimage = "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";

mysql_query($insertimage);

score 0 · Answer 7 · answered Oct 21 '14 at 10:22

Concat the php variables like this:

$insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES (" . $filenamec . "," . $text . ", " . $timestamp . ", " . $countdown . ")";

with the respective single quotes in those that are text fields i.e: "... '" . $text . "' ..."

PHP: Error when inserting quotation marks in mySQL

7 Answers7