escaping strings for SQLite3 in PHP5

Question

Why does both functions fail me? Or is this just an illusion?

<?php
echo sqlite_escape_string('Hello "World" \'\' ...');
echo "\n";
echo SQLite3::escapeString('Hello "World" \'\' ...');
echo "\n";
?>

outputs:

Hello "World" '''' ...
Hello "World" '''' ...

• Phil: Looks OK to me, define "fail". Also, you might want to try PDO, prepared statements and parameter binding.

Answer 1

You should be using PDO to access your database because it has prepared statements which are safer than escaping and also faster.

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

Another big adventage when using PDO is that you can switch between databases(MySQL vs PostGRESQL vs SQLite for example) easily without changing much of your code.

A quick introduction how to use PDO can be read over at nettuts. A very good read/introduction if you ask me!

• Pwnna: I thought SQLite also had prepared statements.
• Alfred: SQLite has prepared statement thanks to PDO for example ;). You can use SQLite via PDO.
• andude: SQLite 3 has escapeString() method, so I just use it now.
• user2975337: I always feel bad downvoting (and I'll undo it if I'm wrong). But this doesn't answer OP's question. He's asking the proper way to escape strings with SQLite3. 'you should be using PDO' doesn't answer that. Note that SQLite3 has prepared statements and parameter binding thanks to sqlite. [afaiu] It's conceivable - however unusual- that someone may have access to SQLite3 without the PDO extension being available.

Answer 2

String literals in SQLite are enclosed in single-quotes, so SQLite3::escapeString() does what it's supposed to: Return a string to be enclosed in single-quotes (except for NUL characters).

If you were looking for a way to escape an identifier ("example", [example] or `example`), there seems to exist no predefined function to do that. But SQLite accepts a string literal as identifier in places where a string literal is not allowed:

If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.

Source: https://www.sqlite.org/lang_keywords.html

That means queries like the following are valid:

$sqlite->exec("CREATE TABLE '" . SQLite3::escapeString("very \" odd ' name") . "' (example)");