PHP Prevent SQL Injection

Question

I'm trying to prevent SQL Injection using PHP with PDO. I've used this as a reference. http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers . My code doesn't give me any error but the values that are getting in are all null.

The vlaues I'm trying to insert are not null. I know this because I've echo'ed them out: echo "\nDate: ".$date." Name: ".$name." mail: ".$mail."Comment: ".$comment." website: ".$website;

$sql = "INSERT post SET timeDate = :timeDate and name = :name and mail = :mail and comment = :comment and website = :website";
$stmt = $db->prepare($sql);
$stmt->bindParam(":timeDate", $date);
$stmt->bindParam(":name", $name);
$stmt->bindParam(":mail", $mail);
$stmt->bindParam(":comment", $comment);
$stmt->bindParam(":website", $website);
$stmt->execute();

On a side note, you don't actually need such a long and winding code, two lines are actually enough: [PDO Examples: UPDATE query using PDO](https://phpdelusions.net/pdo_examples/update) — Your Common Sense, Oct 11 '17 at 11:50
`$sql = "INSERT post SET name=?, mail=?";` `$db->prepare($sql)->execute([$name, $mail]);` But Is this Injection safe? — Fig, Oct 11 '17 at 12:03
@YourCommonSense, MySQL supports a nonstandard syntax for a single-row `INSERT...SET` that is similar to `UPDATE` syntax. See https://dev.mysql.com/doc/refman/5.7/en/insert.html — Bill Karwin, Oct 13 '17 at 04:30

Bill Karwin · Answer 1 · 2017-10-13T16:57:38.163

Don't use AND in between your assignments—use commas.

$sql = "INSERT post
        SET timeDate = :timeDate,
            name = :name,
            mail = :mail,
            comment = :comment,
            website = :website";

Your statement using AND between the terms is no error, because the statement is actually valid. It just doesn't do what you thought it would.

It's as if you did this:

SET timeDate = (:timeDate and name = :name and mail = :mail and comment = :comment and website = :website")

This sets only timeDate to the result of one long boolean expression.

The other columns are not getting assigned anything, they're just being compared to parameterized values. Since this is a new row you haven't inserted yet, all the other columns are naturally NULL, so the comparisons will be NULL. Therefore AND-ing them together will be NULL, and that's the ultimate value that will be assigned to your timeDate column.

The other columns are not assigned any value in this statement, and their default is presumably NULL.

This is a weird and useless statement, but strictly speaking, it's not an error.

I also encourage you to use PDO more simply. Instead of using bindParam() for everything, you can pass an array to execute(). This does the same thing as if you had done bindValue() for each of the parameters. You can do this with named parameters or positional parameters.

$stmt = $db->prepare($sql);
$stmt->execute([
  "timeDate" => $date,
  "name" => $name,
  "mail" => $mail,
  "comment" => $comment,
  "website" => $website]);

This is especially handy if you already have your parameter values stored in an array.

The protection against SQL injection is just as good as using bindParam().

PHP Prevent SQL Injection

1 Answers1