0

I am using phpmyadmin to create a table for an assignment and I am trying to figure out how to prevent SQL injection but I'm getting some weird error and I am not sure why. I am trying to use the quote method to fix it but it's not working. Here is the code I have

try {
  $conn = new PDO("mysql:dbname=*removed*;host=*removed*", "*removed*", "*removed*");
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $firstName = $conn->quote($_POST["first_name"]);
  $lastName = $conn->quote($_POST["last_name"]);
  $house = $conn->quote($_POST["house"]);

  $sql = "INSERT INTO my_table (last_name, first_name, house)
  VALUES ('$lastName', '$firstName', '$house')";
  $conn->exec($sql);
  echo "<h3 id=\"success\">New entry successfully created.</h3>";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

I looked at the textbook I am using and it says to use the $db->quote($_POST["var_name"]); to clean up the data, which is exactly what I do, but for some reason it's not working for me.

EDIT: The error message I am getting says this:

"INSERT INTO my_table (last_name, first_name, house) VALUES (''Allen'', ''Barry'', 'House1')
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'Allen'', ''Barry'', 'House1')' at line 2

Any idea why?

GenericUser01
  • 337
  • 3
  • 11
  • 27
  • 1
    Could you elaborate on "not working"? You mention an error in the title. Care to post the error message? – Siguza Apr 13 '16 at 21:59
  • @Siguza I updated the post and added the error message I was getting. – GenericUser01 Apr 13 '16 at 22:02
  • That textbook gives bad advice. Read http://php.net/manual/en/pdo.quote.php – nobody Apr 13 '16 at 22:03
  • 2
    It is almost never appropriate to use `quote()` and `exec()` in PDO. You should be using `prepare()/execute()` with named parameters. PDO's `quote()` works differently than most other APIs in that it adds quotes, meaning you cannot surround the resultant values in quotes lest they become doubled as yours did. But see [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for examples on how this should be done. – Michael Berkowski Apr 13 '16 at 22:04
  • Your prepared statement should look like `$stmt = $conn->prepare("INSERT INTO my_table (last_name, first_name, house) VALUES (:lastName, :firstName, :house)";` and then call `$stmt->execute()` passing an associative array of the values from `$_POST`. That is the proper way to prevent SQL injection in modern PHP practice. – Michael Berkowski Apr 13 '16 at 22:07
  • You're doing PDO and you don't bind the params? Naughty! – Alon Eitan Apr 13 '16 at 22:08
  • remove the quotes around the variables in your `VALUES()` part of `INSERT` – RST Apr 13 '16 at 22:19

0 Answers0