Using prepared statements with SQLite3 and PHP

Question

I'm trying to add data to a database using SQLite3 in PHP. I got it working without prepared statements but now I'm trying to make it safer. I'm not using PDO.

So far the following code doesn't work. It just inserts the words ":name" and ":email" into the database, instead of what their bound values should be:

$smt = $db->prepare("insert into names (name, email) values (':name', ':email')");
$smt->bindValue(':name', $var_name);
$smt->bindValue(':email', $var_email);

$var_name = ($_POST[post_name]);
$var_email = ($_POST[post_email]);

$smt->execute();

So I thought at first that this was because I have single quotes around :name and :email in the prepared statement. So I took those out. Now when I post the form, it just puts blank entries into the database, it doesn't insert the values of $var_name and $var_email

The statement is executing, it's just not binding the variables properly I don't think. What have I done wrong?

Answer 1

You managed to confuse binding functions.

It is bindParam have to be used if you don't have your variable assigned yet.
While bindValue have to be used with existing value only.

Also, you should turn error reporting ON

Answer 2

You don't need intermediate variables, you must do this:

$smt = $db->prepare("insert into names (name, email) values (':name', ':email')");
$smt->bindValue(':name', $_POST['post_name'], SQLITE3_TEXT);
$smt->bindValue(':email', $_POST['post_email'], SQLITE3_TEXT);

$smt->execute();

As documented in SQLite3Stmt::bindValue() value is binded instantly, not as SQLite3Stmt::bindParam() that gets the value of the variable at execute() time. So the problem is that that variables are empty when the statement is executed.

---

Remember:

• You don't need to add parentheses on variable assignment: $a = ($b); -> $a = $b;
• You MUST quote variable key name. Otherwise PHP will try to look for a constant with this name and will throw a warning if it doesn't exists... but will assign a erroneous key value if it exists!! $_POST[post_name] -> $_POST['post_name']