trouble with quotes and mysql insert into

Question

I'm having problems inserting a form $_POST variable to MySQL! I know it's a single quote problem but simply cannot resolve it.

Code is:

$naziv_db = $_POST["naziv"];
$naziv_db = mysql_real_escape_string($naziv_db);

$query = "INSERT INTO items (title) VALUES ('$naziv_db')";

$stmt = mysql_query($query) or die("MySQL error: " . mysql_error());

If I enter a value containing " it inserts correctly, but if it contains ' then the error appears!

For example if my input is Milky's

error is: 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 's

If my input is "Milkys" everything goes well...

I'm new here, so can't post an answer to my own question so i have to edit!

Christian's solution was the right one!

I have changed the code:

$query = "INSERT INTO items (title) VALUES ('$naziv_db')";

to:

$query = 'INSERT INTO `items` (`title`) VALUES ("'.$naziv_db.'")';

and now it accepts both " and ' without error!

Thank you guys, you're the best :D

I would use: ``$query = 'INSERT INTO `items` (`title`) VALUES ("'.$naziv_db.'")';`` — Christian, Apr 14 '11 at 14:58
it cannot be an issue. may be you had an error somewhere else and accidentally corrected it while implementing this useless change in your code. — Your Common Sense, Apr 14 '11 at 15:21
could easily be... but the main thing is that it works as it supposed to now... you were right, my code is not exactly the same as i wrote, it has a couple more values, but they weren't producing any errors, only singlequote input... — marko, Apr 14 '11 at 15:28

score 2 · Accepted Answer · edited May 23 '17 at 12:13

2

To avoid this entirely, you'd be best using a prepared statement.

There's a good example in the answer to this question.

Converted for your case, you get:

$db = new mysqli("host","user","pw","database");
$stmt = $db->prepare("INSERT INTO items (title) VALUES (?)");
$stmt->bind_param('s', $_POST["naziv"]);
$stmt->execute();
$stmt->close();

edited May 23 '17 at 12:13

Community

1
1

answered Apr 14 '11 at 14:57

Simon

1,980
14
21

Your Common Sense · Answer 2 · 2011-04-14T15:17:23.937

It's quite impossible to get such an error from your code.
Most likely there is a typo somewhere in it.
May be you're escaping wrong variable or it's another query producing this error

Are you sure you posted the code you actually running? is it exact code or some sketch?

change your mysql_query string to this one

mysql_query($query) or trigger_error(mysql_error()." ".$sql);

and paste it's output please.

or, even change whole code:

ini_set('display_errors',1);
error_reporting(E_ALL);

$naziv_db = $_POST["naziv"];
$naziv_db = mysql_real_escape_string($naziv_db);
$query = "INSERT INTO items (title) VALUES ('$naziv_db')";

var_dump($_POST["naziv"]);
echo "<br>\n";
var_dump($naziv_db);
echo "<br>\n";
var_dump($query);
echo "<br>\n";

mysql_query($query) or trigger_error(mysql_error()." ".$sql);

this is called "debugging" and usually helps.

the main problem wasn't the var naziv, it was another var that contained html code, that var wasn't filtered through mysql_real_escape_string() so it was returning an error! thank you for pointing this to me... respect! — marko, Apr 14 '11 at 16:10

score -4 · Answer 3 · answered Apr 14 '11 at 14:52

-4

Try addslashes - it's made for parsing strings into database-friendly content.

answered Apr 14 '11 at 14:52

Tim

749
4
14

3

That's absolutely not true. If `addslashes` were used in the above context, it would cause SQLi (sql injection). – Christian Apr 14 '11 at 14:55
2

Addslashes is unlikley to produce a different result, and the [debate about whether it's better than `mysql_real_escape_string()`](http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string) usually fell more on the "use `mysql_real_escape_string()`" side of the fence. – Simon Apr 14 '11 at 14:55
@Christian there is only a microscopic chance for it. A negligible one. barely worth to mention it. Moreover, without setting proper encoding it would be the the same as addslashes – Your Common Sense Apr 14 '11 at 15:12
@Col. Shrapnel - Fuzzers don't care about "microscopic chance"s, they just get to it eventually. – Christian Apr 14 '11 at 15:39
@Christian dunno who them fuzzers are but they can't get into utf-8 nor any single-byte encoding, so, there is only microscopic chance. – Your Common Sense Apr 14 '11 at 16:28

trouble with quotes and mysql insert into

3 Answers3