CODEWITHSUNDEEP
csqliteescapingquotes
gavinbeatty
gavinbeatty

Reputation: 319

Is there any C SQLite API for quoting/escaping the name of a table?

It's impossible to sqlite3_bind_text a table name because sqlite3_prepare_v2 fails to prepare a statement such as:

SELECT * FROM ? ;

I presume the table name is needed to parse the statement, so the quoting needs to have happened before sqlite3_prepare_v2.

Is there something like a sqlite3_quote_tablename? Maybe it already exists under a name I can't recognize, but I can't find anything in the functions list.

Upvotes: 9

Views: 2380

Answers (6)

Björn Höhrmann
Björn Höhrmann

Reputation: 470

SQLite will escape identifiers for you with the %w format in the https://www.sqlite.org/printf.html family of functions.

Upvotes: 2

momeara
momeara

Reputation: 1381

your proposed sqlite3_quote_tablename function could sanitize the input to prevent sql injection attacks. To do this it could parse the input to make sure it is a string literal. http://sqlite.org/lang_expr.html#litvalue

Upvotes: 1

Julian
Julian

Reputation: 9130

I was looking for something like this too and couldn't find it either. In my case, the expected table names were always among a fixed set of tables (so those were easy to validate). The field names on the other hand weren't so I ended up filtering the string, pretty much removing everything that was not a letter, number, or underscore (I knew my fields would fit this parameters). That did the trick.

Upvotes: 0

Mike Willekes
Mike Willekes

Reputation: 6150

When using SQLite prepared statements with parameters the parameter: "specifies a placeholder in the expression for a literal value that is filled in at runtime"

Before executing any SQL statement, SQLite "compiles" the SQL string into a series of opcodes that are executed by an internal Virtual Machine. The table names and column names upon which the SQL statement operates are a necessary part of the compilation process.

You can use parameters to bind "values" to prepared statements like this:

SELECT * FROM FOO WHERE name=?;

And then call sqlite3_bind_text() to bind the string gavinbeatty to the already compiled statement. However, this architecture means that you cannot use parameters like this:

SELECT * FROM ? WHERE name=?;    // Can't bind table name as a parameter
SELECT * FROM FOO WHERE ?=10;    // Can't bind column name as a parameter

Upvotes: 1

David
David

Reputation: 2803

If SQLite doesn't accept table names as parameters, I don't think there is a solution for your problem...

Take into account that:

Parameters that are not assigned values using sqlite3_bind() are treated as NULL.

so in the case of your query, the table name would be NULL which of course is invalid.

Upvotes: 0

Samuel Neff
Samuel Neff

Reputation: 74909

If a table name has invalid characters in it you can enclose the table name in double quotes, like this.

sqlite> create table "test table" (id);
sqlite> insert into "test table" values (1);
sqlite> select * from "test table";
id
----------
1

Of course you should avoid using invalid characters whenever possible. It complicates development and is almost always unnecessary (IMO the only time it is necessary is when you inherit a project that is already done this way and it's too big to change).

Upvotes: 1

Related Questions