How to escape string in System.Data.SQLite without prepared statements

Question

How can I escape strings in System.Data.SQLite without using parameters? I'm building a potentially large INSERT query like so.

string sql = "INSERT INTO Request (Col1, Col2, Col3) VALUES ";
bool hasValue = false;
foreach (TestItem item in order.Tests)
{
    if (hasValue)
        sql += ", ";
    hasValue = true;

    sql = sql + String.Format("('{1}', '{2}', '{3}')", sql, a, b, c);
}

using (SQLiteCommand command = new SQLiteCommand(sql, _database))
    command.ExecuteNonQuery();

I could break each insert into a separate query, but wouldn't it be more efficient to escape the strings myself? I could generate query parameters dynamically, but couldn't the parameter list get too large?

use parameters, you wont overflow anything

pm100
– pm100

2017-02-08 21:14:47 +00:00
Commented Feb 8, 2017 at 21:14 — pm100
– pm100, Commented Feb 8, 2017 at 21:14
if you must do this use stringbuilder

pm100
– pm100

2017-02-08 21:16:11 +00:00
Commented Feb 8, 2017 at 21:16 — pm100
– pm100, Commented Feb 8, 2017 at 21:16

ryancdotnet · Accepted Answer · 2017-02-08 21:17:27Z

2

If efficiency is your concern, I would look at performing multiple inserts separately, with parameters (for safety), but within a Transaction. SQLite performance is terrible when inserting row by row, but if you can wrap all inserts inside a transaction, you will see much better performance.

Here's a good article on this:

https://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/

And for futher clarification, the article deep links to this FAQ direct from SQLite, which I find helpful:

http://www.sqlite.org/faq.html#q19

answered Feb 8, 2017 at 21:17

ryancdotnet

2,42021 silver badges35 bronze badges

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

1 Comment

milleniumbug Over a year ago

+1 For answering the underlying question instead of attempting to provide a solution to the suggested workaround.

Collectives™ on Stack Overflow

How to escape string in System.Data.SQLite without prepared statements

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