Pwnna
Pwnna

Reputation: 9538

escaping strings for SQLite3 in PHP5

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" '''' ...

Upvotes: 6

Views: 8781

Answers (2)

Kontrollfreak
Kontrollfreak

Reputation: 1830

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)");

Upvotes: 0

Alfred
Alfred

Reputation: 61803

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!

Upvotes: 5

Related Questions