Preparing SQLite SQL statements in PHP

Question

I'm trying how best to prepare my SQLite SQL strings in PHP. The SQLite3 class comes with an escapeString() function, but here are my issues:

Try 1)

$sql = "INSERT INTO items ('id','content','title','created') VALUES ('4e7ce7c18aac8', 'Does this work', NULL, '2011-09-23T16:10:41-04:00');";
$sql = SQLite3::escapeString( $sql );
echo ($sql);

This results in a string that's all jacked up:

INSERT INTO items (''id'',''content'',''title'',''created'') VALUES (''4e7ce7c18aac8'', ''Does this work'', NULL, ''2011-09-23T16:10:41-04:00'');

Those aren't double quotes, rather doubled-up single quotes. Obviously won't work.

---

Try 2)

$sql = 'INSERT INTO items ("id","content","title","created") VALUES ("4e7ce7c18aac8", "Does this work", NULL, "2011-09-23T16:10:41-04:00");';
$sql = SQLite3::escapeString( $sql );
echo ($sql);

This results in:

INSERT INTO items ("id","content","title","created") VALUES ("4e7ce7c18aac8", "Does this work", NULL, "2011-09-23T16:10:41-04:00");

This query works fine, but the escapeString function hasn't modified anything as there's nothing to escape...

---

Try 3)

$sql = 'INSERT INTO items ("id","content","title","created") VALUES ("4e7ce7c18aac8", "Doesn't this work", NULL, "2011-09-23T16:10:41-04:00");'; $sql = SQLite3::escapeString( $sql ); echo ($sql);

Here's the big problem- Now I have an apostrophe in one of my values. It won't even make it to escapeString() because PHP will throw an error on the invalid string:

PHP Parse error: syntax error, unexpected T_VARIABLE, expecting ',' or ';'

---

How am I supposed to be approaching this? Keep in mind that in the actual code my parameter values will be variables, so am I supposed to escape each variable before I pass it into the string? If so, what function do I use?

Finally, what's the point of escapeString()?? I can't figure out how it's supposed to be used correctly.

Answer 1

You don't escape the entire query. You escape unsafe data you're inserting into the query, e.g.

$unsafe = $_GET['nastyvar'];
$safe = SQLite3::escapeString($unsafe);
$sql = "INSERT INTO table (field) VALUES ($safe);";
echo ($sql);