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?

Maybe, adding a type of data will help: SQLITE3_TEXT? Though, it's optional. http://www.php.net/manual/en/sqlite3stmt.bindvalue.php — user4035, Aug 28 '13 at 10:14
Yes I forgot to mention that I have tried that too, thanks though — Conor Taylor, Aug 28 '13 at 10:17

score 6 · Accepted Answer · edited Feb 04 '18 at 23:43

6

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

edited Feb 04 '18 at 23:43

amenthes

3,117
1
32
38

answered Aug 28 '13 at 10:23

Your Common Sense

156,878
40
214
345

Oh wait, execute only accepts parameters when using PDO, I said in my question that I was not using PDO.. How can I get it to work WITHOUT PDO? – Conor Taylor Aug 28 '13 at 11:09
1

Well, then read aforementioned functions descriptions and use them properly – Your Common Sense Aug 28 '13 at 11:18

OscarGarcia · Answer 2 · 2017-06-05T06:12:57.097

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']

Using prepared statements with SQLite3 and PHP

2 Answers2

Linked