Using SQLite trigger with argument

Question

I'm trying to prevent an injection in my trigger (create or not depending on a preference).

I'm using

public void execSQL (String sql, Object[] bindArgs)

I have my Trigger with one "?" and pass my value in the bindArgs.

The problem is that I receive an error during the creation of the trigger

03-13 13:43:49.136: E/SQLiteLog(21865): (1) trigger cannot use variables
03-13 13:43:49.137: W/System.err(21865): android.database.sqlite.SQLiteException: trigger cannot use variables (code 1): , while compiling: 
CREATE TRIGGER IF NOT EXISTS 
   my_trigger
   INSERT ON table_1 BEGIN DELETE 
   FROM table_2 
   WHERE col1 = new.col1 
      AND col2 = ?
   END;

It looks like I cannot use argument with a create trigger query but I don't understand why. If I replace the "?" myself with a value in the String and use

public void execSQL (String sql)

This work just fine but I would like to create a trigger with a String value an I don't want to check for SQLInjection.

Anyone to explain why I can't use

public void execSQL (String sql, Object[] bindArgs)

for a trigger (even if the android documentation says otherwise)

For example, the following are good candidates for using this method:

• CREATE or DROP table / trigger / view / index / virtual table

• ...

Answer 1

The error message is quite explicit:

trigger cannot use variables

i.e. you cannot use ? variables in a trigger.

Just use string formatting with no variables. Since the parameter is an integer, there's no need to sanitize it for injection, assuming you're passing it around as an integer.

For strings there is DatabaseUtils.sqlEscapeStrings().

---

"the following are good candidates for using this method: CREATE or DROP trigger"

This really means that either one of the execSQL() overloads should be used for SQL like that, and there are no better alternatives in the Android SQLite wrapper. It does not mean that variable bindings specifically are good to be used with such SQL.

The documentation of Android sqlite is not very good.

Is it not Android who "replace" the '?'s to send a correct query to SQLite ?

Nope, the ? parameters are bound in sqlite.