views:

190

answers:

4

Hi all, I'm quite new to PHP so sorry if sounds such an easy problem... :)

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');

Could you provide any solution please?

Thanks in advance.

Mauro

+3  A: 

it should work without the sprintf stuff

$title = mysql_real_escape_string($_POST[title]);
$body = mysql_real_escape_string($_POST[body]);
Tim
Thank you, it works like a charm!
Mauro74
@Mauro: Still you should not use this, but parameterized statements instead.
Tomalak
@Tomalak not "should" but "recommended". Prepared statements are more fool-proof, yes, but still not a silver bullet.
Col. Shrapnel
@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
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
@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
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
+1  A: 

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.

Tommy
Thanks I'll have a look at that! :)
Mauro74
+1  A: 

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

Salil
yes I know about the comma was just a typo.
Mauro74
+5  A: 

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);
Tomalak