PHP PDO Insert query with prepared statements

Question

I am trying to run an sql query using PDO prepared statements

$sql = "INSERT INTO tickets (ticketnumber, status) VALUES (1234, Open) ";
$stmt = $connection->prepare($sql);
$stmt->execute();

But it is just not inserting. What have I done wrong?

Here is my connection:

$host = "localhost";
$db_name = "";
$username = "";
$password = "";
$connection = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);

possible duplicate of [How to squeeze error message out of PDO?](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo) — Michael Berkowski, Dec 30 '13 at 14:50
See also [MySQL when to use single quotes, double quotes, backticks](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Michael Berkowski, Dec 30 '13 at 14:50
This isn't really a prepared statement; the values to be inserted should be `:ticketnumber :status` and then provided. — Digital Chris, Dec 30 '13 at 14:52
I didn't want to put the connection details on here! Surely it's a little obvious! — charlie, Dec 30 '13 at 17:42

Shahlin Ibrahim · Answer 1 · 2013-12-30T18:26:58.513

3

Try this. It's much more secure. Make sure you have included your connection file.

EDITED

$sql = "INSERT INTO `tickets` (ticketnumber, status) VALUES (:ticketnumber, :status)";
$stmt = $connection->prepare($sql);
$stmt->bindValue(':ticketnumber', 1234, PDO::PARAM_INT);
$stmt->bindValue(':status', 'Open', PDO::PARAM_STR);
$stmt->execute();

Also, the named parameters used above must NOT be enclosed in quotes. If you do so, it'll be treated as a literal string and not a named parameter.

edited Dec 30 '13 at 18:26

answered Dec 30 '13 at 15:03

Shahlin Ibrahim

1,049
1
10
20

So for every column and value I have to bind the value ? – charlie Dec 30 '13 at 17:24
how come it is more secure? – Your Common Sense Dec 30 '13 at 18:38
@charlie of course not. for the values that are coming from variables only. – Your Common Sense Dec 30 '13 at 18:40
@YourCommonSense If he's using it as it is, it might be prone to SQL Injections right? Assuming its a variable containing user input. Because inserting data like this manually is not always used. – Shahlin Ibrahim Dec 30 '13 at 18:58

score 2 · Answer 2 · answered Dec 30 '13 at 14:50

2

You need to use quotes on strings before inserting them into a database.
Why use prepare if you're not preparing your data before sending it to the database?

answered Dec 30 '13 at 14:50

Marco Aurélio Deleu

4,279
4
35
63

PHP PDO Insert query with prepared statements

2 Answers2