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

...

laalto · Accepted Answer · 2015-03-13 13:36:52Z

4

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.

edited Mar 13, 2015 at 13:36

answered Mar 13, 2015 at 12:55

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AxelH Over a year ago

Well, I just have to create a good method to prevent Injection and I will be good. Thanks again!

Collectives™ on Stack Overflow

Using SQLite trigger with argument

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related