Sqlite Binding Within String Literal

Sqlite binding within string literal

SELECT * FROM table WHERE title LIKE '%' || ? || '%';

How to bind literal text to an SQLite query?

Don't put parameter placeholders inside quotes, even if the value is a string or date literal.

const char *sql = "SELECT COUNT(*) FROM tasks WHERE completed > ?";

Which characters must be escaped to obtain a legal SQLite string literal?

Assuming PRAGMA encoding="UTF-8"; is in effect, the only (Unicode) characters to be concerned about are the single-quote and NULL (U+0000).

If you want unicode escapes to be replaced by the corresponding characters, you can use SQLite's json_extract function. Here are some examples:

Input:

select 'a
b'; 

select 'é';

select json_extract('"\u00e9"','$');

Output:

a
b
é
é

NUL

Literal NULs seem to vanish at the sqlite3 command-line prompt, in the sense that:

select length('a' || 'x^@y' || 'b'); -- where ^@ stands for NUL
4

However, using a different input method:

 select ('a' || 'x�y' || 'b');
 ax�yb

SQLite FTS5 through PHP/PDO - How to bind values while filtering on a column name?

You can't have parameters in string literals; there's no interpolation done looking for them. You can, however, build an argument to MATCH using string concatenation:

$sql = "SELECT * FROM ftstable WHERE ftstable MATCH ('colname: ' || :keyword)";

Escape single quote character for use in an SQLite query

Try doubling up the single quotes (many databases expect it that way), so it would be :

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there''s');

Relevant quote from the documentation:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. ... A literal value can also be the token "NULL".

Like statement with quotes in sqlite database query?

Maybe this will help you:

Cursor c = myDB.query(MY_DATABASE_TABLE, "songname","songname like ?" , new String[]{"%"+MATCH_STRING+"%"}, null,"SongHit", null);

sqlite LIKE problem in android

I think you shouldn't use selArgs for LIKE such a way. You may try this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word"+" LIKE '"+name+"%'", null, null, null, null);

EDIT:

OK, if you want be safe from SQL injections, don't use above solution, use this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word LIKE '?'", new String[]{name+"%"}, null, null, null);

Python sqlite3 string formatting

In the first case you are doing parameter binding. Which is correct, sqlite libraries will bind the value 55 to the query call. In the second case you should not use parameter binding because the sql will be something equivalent to:

SELECT * FROM my_table WHERE id='associated_id'

Because the binding call detects that you are passing a string and then it treats it internally like a string.

When you do

"SELECT * FROM my_table WHERE id=%s" % str(id)

You don't do parameter binding but simply pass the SQL as it is to sqlite.

Some documentation on parameter binding in:

http://www.sqlite.org/c3ref/bind_blob.html

Android SQLite : Issue with delete statement prepared query

You can use ? only to bind literals, not identifiers such as table or column names. When binding literals, the ? must not be quoted itself ('?' is a string literal containing ?, not a placeholder.)

I don't really see the point of attempting to use delete() if you already have a functioning execSQL() raw SQL. You can use ? placeholders with execSQL(), too, for example for the string literals such as name.

---

Related Topics