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?

Answer 1

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);