-1

How do you escape strings for SQLite table names in c?

I find a document, but it do not tell me the detail https://www.sqlite.org/lang_keywords.html

And this document says that sql is end with '\x00' https://www.sqlite.org/c3ref/prepare.html

Here is the similar question in python: How do you escape strings for SQLite table/column names in Python?

bronze man
  • 1,470
  • 2
  • 15
  • 28

2 Answers2

0

Example:

  • example to 'example'
  • 'a to '''a'

Detail:

  • Do not use the byte value 0 in the string.It will return an error like unrecognized token: "'" even if you pass the correct zSql len to sqlite3_prepare_v2().
  • Replace ' to '' in the string, add ' to the start and the end to the string. ' is single quote (byte 39).

  • It is not recommend to use invalid utf8 string. The string do not need to be valid utf8 string according to the parse code in sqlite3 version 3.28.0 . I have tested that invalid utf8 string can use as table name, but the document of sqlite3_prepare_v2() says you need SQL statement, UTF-8 encoded

  • I have write a program to confirm that any byte list with len 1,2 without byte value 0 in it, can use as the table name, and the program can read value from that table, can list the table from the SQLITE_MASTER table in sqlite3 version 3.28.0.

bronze man
  • 1,470
  • 2
  • 15
  • 28
0

Identifiers should be wrapped in double quotes if they need escaping, with all double quotes in them escaped by doubling up the quotes. bad"name" needs to become "bad""name" to be used in a SQL statement.

Sqlite comes with custom versions of *printf() functions that include formats for escaping sql identifiers and strings (Which use single quotes in SQL). The one that does the escaping of double quotes for identifiers is %w:

char *sanitized_ddl = sqlite3_mprintf("CREATE TABLE \"%w\"(\"%w\", \"%w\");",
                                      "bad\"name", "foo bar", "baz");

Ideally, though, you're not going to use table or column names that need escaping, but it's good practice to escape user-supplied names to help protect against SQL injection attacks and the like.

Shawn
  • 47,241
  • 3
  • 26
  • 60
  • "Ideally, though, you're not going to use table or column names that need escaping" No, I will not escape user-supplied names in the sql. I will always use the sqlite3_bind_blob() to pass my data into the sqlite3, but I can not use sqlite3_bind_blob to pass the table name. so I need find a way to escape them or encode them with hex. – bronze man Oct 26 '19 at 05:14
  • "Identifiers should be wrapped in double quotes if they need escaping" where this message come from? As the c use double quotes already , i would like to use single quotes. And there is a compile options to disable double quotes: https://www.sqlite.org/compile.html#dqs . I think double quotes is not first choose. – bronze man Oct 26 '19 at 05:24