How to escape quotes when inserting into database with PHP

Question

I'm having an error message when inserting content which contains quotes into my db. here's what I tried trying to escape the quotes but didn't work:

$con = mysql_connect("localhost","xxxx","xxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);

$nowdate = date('d-m-Y')

$title =  sprintf($_POST[title], mysql_real_escape_string($_POST[title]));

$body = sprintf($_POST[body], mysql_real_escape_string($_POST[body]));

$sql="INSERT INTO articles (title, body, date) VALUES ('$title','$body','$nowdate'),";

if (!mysql_query($sql,$con))
  {
  
die('Error: ' . mysql_error());
  
}

header('Location: index.php');

Please show the error message, you seem to be already escaping the data correctly. — Pekka, Apr 07 '10 at 12:07

Tomalak · Answer 1 · 2010-04-07T12:29:43.773

Please start using prepared parameterized statements. They remove the need for any SQL escaping woes and close the SQL injection loophole that string-concatenated SQL statements leave open. Plus they are much more pleasing to work with and much faster when used in a loop.

$con  = new mysqli("localhost", "u", "p", "test");
if (mysqli_connect_errno()) die(mysqli_connect_error());

$sql  = "INSERT INTO articles (title, body, date) VALUES (?, ?, NOW())";
$stmt = $con->prepare($sql);
$ok   = $stmt->bind_param("ss", $_POST[title], $_POST[body]);

if ($ok && $stmt->execute())
  header('Location: index.php');
else
  die('Error: '.$con->error);

score 12 · Accepted Answer · answered Apr 07 '10 at 12:06

12

it should work without the sprintf stuff

$title = mysql_real_escape_string($_POST[title]);
$body = mysql_real_escape_string($_POST[body]);

answered Apr 07 '10 at 12:06

Flatlin3

1,658
14
26

1

@Mauro: Still you should not use this, but parameterized statements instead. – Tomalak Apr 07 '10 at 12:24
1

@Tomalak not "should" but "recommended". Prepared statements are more fool-proof, yes, but still not a silver bullet. – Your Common Sense Apr 07 '10 at 12:26
1

@Col. Shrapnel: I thought "should" and "recommended" was the same thing. I would have used some form of "have to" or "must" otherwise. *Sorry, I'm not into watering down language constructs purely for the sake of politeness.* ;) – Tomalak Apr 07 '10 at 12:33
Whilst parameterised statements are definitely a better approach in general, unfortunately PHP's `mysqli_bind_param` implementation of them is a bit verbose, and has a disastrous interface trap for the unwary in that it binds by variable reference instead of value. This often makes it a more difficult sell than escaping. (PDO is a bit better on this front.) – bobince Apr 07 '10 at 13:09
@bobince: Binding by reference should not be a problem as long as binding and execution are done in close succession. Apart from that it is bad style to re-use dedicated variables for something else anyway. ;) For my part, verbosity+security beats brevity. Code is more often read than written, so being verbose is actually a good thing. – Tomalak Apr 07 '10 at 13:18
It's not a problem as long as you know about it. For a beginner (and, I'd wager, a majority of everyday PHP coders) who don't, it's a counter-intuitive and potentially annoying-to-debug trap. It also stops you binding a value like `'foo'.$bar`, which means more bogus temporary variables. I love parameterised queries, but `mysqli`'s interface is a shed. Python's DB-API shows how it should have be done concisely. (Although then you get the heartache of `paramstyle`, so you can't win I guess...) – bobince Apr 07 '10 at 13:27

score 2 · Answer 3 · answered Apr 07 '10 at 12:08

2

With any database query, especially inserts from a web based application, you should really be using parameters. See here for PHP help on how to use parameters in your queries: PHP parameters

This will help to prevent SQL injection attacks as well as prevent you from having to escape characters.

answered Apr 07 '10 at 12:08

Tommy

39,592
10
90
121

Thanks I'll have a look at that! :) – Mauro74 Apr 07 '10 at 12:19

score 2 · Answer 4 · answered Apr 07 '10 at 12:11

2

Your code

$sql="INSERT INTO articles (title, body, date) VALUES ('$title','$body','$nowdate'),";

should be as follows

$sql="INSERT INTO articles (title, body, date) VALUES ('$title','$body','$nowdate')";

comma should not be there at the end of query

answered Apr 07 '10 at 12:11

Salil

46,566
21
122
156

How to escape quotes when inserting into database with PHP

4 Answers4