22

I'm having some strange feeling abour sqlite3 parameters that I would like to expose to you.

This is my query and the fail message :

#query
'SELECT id FROM ? WHERE key = ? AND (userid = '0' OR userid = ?) ORDER BY userid DESC LIMIT 1;'
#error message, fails when calling sqlite3_prepare()
error: 'near "?": syntax error'

In my code it looks like:

// Query is a helper class, at creation it does an sqlite3_preprare()
Query q("SELECT id FROM ? WHERE key = ? AND (userid = 0 OR userid = ?) ORDER BY userid DESC LIMIT 1;");
// bind arguments
q.bindString(1, _db_name.c_str() ); // class member, the table name
q.bindString(2, key.c_str()); // function argument (std::string)
q.bindInt   (3, currentID); // function argument (int)
q.execute();

I have the feeling that I can't use sqlite parameters for the table name, but I can't find the confirmation in the Sqlite3 C API.

Do you know what's wrong with my query?
Do I have to pre-process my SQL statement to include the table name before preparing the query?

Gui13
  • 12,993
  • 17
  • 57
  • 104
  • 2
    No, you can't do that, with SQLite or with most other SQL products. Parameter binding is for binding parameters, not replacing any old bit of the query that you fancy. –  May 03 '11 at 13:37
  • Well, that's what I suspected, but the SQLite API doesn't tell about it, do you know where I can have a *confirmation* of this? Also, I disagree that a parameter can't be one of the *variable* of the SQL statement. `INSERT`, `UPDATE`, `WHERE` are static, fixed lexers from the SQL language, but the table name is not, IMHO. – Gui13 May 03 '11 at 13:40
  • 1
    Here http://www.sqlite.org/c3ref/bind_blob.htm, for example says "literals may be replaced by a parameter". Literals in the SQL grammar are things like strings and numbers. Table names are not literals. –  May 03 '11 at 13:44
  • 1
    Table names affect meaning of column names in the rest of the statement, so the parser needs to know them. In this they are like classes in other languages and you can't use variables for classes in most languages either. – Jan Hudec May 04 '11 at 06:11
  • @JanHudec Most statically typed languages, you mean. – JAB Aug 02 '13 at 12:58
  • @JAB: Yes. In fact I meant static types, not classes. Because not all types are classes and classes are not really types in some dynamic languages. – Jan Hudec Aug 02 '13 at 13:27

2 Answers2

18

Ooookay, should have looked more thoroughly on SO.

Answers:
- SQLite Parameters - Not allowing tablename as parameter
- Variable table name in sqlite

They are meant for Python, but I guess the same applies for C++.

tl;dr:

You can't pass the table name as a parameter.
If anyone have a link in the SQLite documentation where I have the confirmation of this, I'll gladly accept the answer.

Community
  • 1
  • 1
Gui13
  • 12,993
  • 17
  • 57
  • 104
  • 3
    The sqlite documentation can be found here: https://www.sqlite.org/cintro.html In chapter 6. Binding Parameters and Reusing Prepared Statements it says `Parameters may not be used for column or table names.` – Bruno Bieri Jun 09 '17 at 14:37
5

I know this is super old already but since your query is just a string you can always append the table name like this in C++:

std::string queryString = "SELECT id FROM " + std::string(_db_name);

or in objective-C:

[@"SELECT id FROM " stringByAppendingString:_db_name];
PirateDave
  • 163
  • 1
  • 3
  • 11
  • 1
    That's right, but it was for an embedded system where I had to limit the memory fragmentation. Doing this would allocate 4 strings in one line, which wouldn't be very efficient. I solved the problem at the time by changing the DB layout to something more suitable. – Gui13 Apr 25 '13 at 18:55
  • @Gui13 Actually, such a line of code would allocate at most 2 blocks: one to store `_db_name`, and perhaps another to store the entire query with `"SELECT ..."` prepended, if the compiler doesn't optimize `operator+(const char*, std::string&&)`, or there's not enough extra space in the temporary. There's no need to convert a `char*` literal to a string before appending it to another string, and in copy-initialization the copy is elided. Furthermore, most compilers use (or have the option to use) the small string optimization, which would mean no allocations at all in most cases. – bcrist Mar 03 '14 at 00:09
  • 6
    More importantly, appending strings exposes your code to sql injection, what if the value of `_db_name` was `"table; delete from table;"` It's a shame we can't use parameters for the tablename, but we must at least sanitize the value or verify that such a table exists first. – ateijelo Jun 22 '16 at 19:53