How to escape a string in system.data.sqlite?

Question

I am executing an SQL query (system.data.SQLite) like so:

var color = "red";
var command = new SQLiteCommand("SELECT something FROM tabletop WHERE color = '" + color + "'", Connection);
var reader = command.ExecuteReader();

The color variable is a text supplied by the user. How can I escape this text to prevent SQL injection? Or is this bad practice and I should execute the query in some entirely different "protected" way?

You have to use the syntax shown in this question http://stackoverflow.com/questions/22015572/sqlite-exception-using-string-format-args-wrong-syntax — aalku, Aug 12 '14 at 10:00

Alastair Campbell · Accepted Answer · 2014-08-12T10:10:06.063

9

You should use parameterized queries:

var command = new SQLiteCommand("SELECT something FROM tabletop WHERE color = @Color", Connection);
command.Parameters.AddWithValue("Color", color);

You can also pass an array of SQLiteParameters into the command.Parameters collection like so:

SQLiteParameter[] parameters = { new SQLiteParameter("Color", color), new SQLiteParameter("Size", size) }; // etc.
command.Parameters.AddRange(parameters);

edited Aug 12 '14 at 10:10

answered Aug 12 '14 at 09:56

Alastair Campbell

648
4
18

Do I need to have `command.Parameters.AddWithValue` line for each variable, or is there some way to pass an array, or some different collection there? – Fred Aug 12 '14 at 10:00

score 2 · Answer 2 · answered Aug 12 '14 at 10:00

You do it with prepared statements:

SQLiteCommand sql = SQLiteDB.CreateCommand();
sql.CommandText = @"INSERT INTO aziende VALUES (@id_azienda, @nome)";

SQLiteParameter lookupValue = new SQLiteParameter("@id_azienda");
SQLiteParameter lookupValue2 = new SQLiteParameter("@nome");

sql.Parameters.Add(lookupValue);
sql.Parameters.Add(lookupValue2);

lookupValue.Value = "Your unsafe user input goes here";
lookupValue2.Value = "Your second unsafe user input goes here";

sql.ExecuteNonQuery();

How to escape a string in system.data.sqlite?

2 Answers2

Linked