Rusqlite: securely inserting multiple rows

Question

I want to insert multiple rows into a sqlite database using Rusqlite. The values may contain single quote characters.

This simple way of inserting multiple rows works as long as none of the values contains a single quote character:

insert into table (field1, field2, field3) 
values ('Value a1', 'Value a2', 'Value a3'), 
       ('Value b1', 'Value b2', 'Value b3')

But as soon as one value contains a single quote, the insert fails (of course).

When inserting a single row, you can securely do it this way:

let sSQL: String  = format!("insert into table(field1, field2, field3) values(?1, ?2, ?3)");
let mut stmt = conn.prepare (&sSQL)?;

stmt.execute([&sValue1, &sValue2, &sValue3]);

My question: what is the best way of securely inserting multiple rows, which may also contain single quotes?

Answer 1

As the example in the documentation for Connection::prepare says, you can just call Statement::execute multiple times to achieve the desired effect:

let sSQL: String  = format!("insert into table(field1, field2, field3) values(?1, ?2, ?3)");
let mut stmt = conn.prepare (&sSQL)?;

stmt.execute([&sValue11, &sValue12, &sValue13])?;
stmt.execute([&sValue21, &sValue22, &sValue23])?;

(Also, don't forget to handle the Results of execute calls too!)

Answer 2

My question: what is the best way of securely inserting multiple rows, which may also contain single quotes?

If you know that you always need to insert 2 rows, you can just expand the existing version:

let sSQL = "insert into table(field1, field2, field3) values (?1, ?2, ?3), (?4, ?5, ?6)";
let mut stmt = conn.prepare (&sSQL)?;

stmt.execute((&v1, &v2, &v3, &v4, &v5, &v6));

Otherwise it's as FZs indicates, you can execute a prepared statement multiple times. In fact that's rather the point of using a prepared statement, otherwise you could just use Connection::execute.

This simple way of inserting multiple rows works as long as none of the values contains a single quote character:

Note that this should generally be considered broken if the query is not a static string. Even if you think you know that all the dynamic data you're injecting is safe, it's an unnecessary risk.

When inserting a single row, you can securely do it this way:

Note that numbering the placeholders is not a requirement, ? will work just as well and will work as if they were numbered sequentially. The main advantages of explicit numbering are:

• Keeping track on large queries with many parameters.
• Using the same parameter multiple times.
• If for some reason you need to alter the order of parameters in the query without being able to change that of the parameters themselves.

But in those situations (possibly aside from the last) you may want to use named parameters instead anyway.

I would also recommend using a tuple of parameters in all cases but sending a single parameter: an array or slice will not allow different parameter types, so might as well consistently use tuples.