13

I have a text field and its breaking my sql statement. How do i escape all the chars in that field? I am using sqlite with http://sqlite.phxsoftware.com/ in C#

2 Answers2

33

You should be using a parameter as in:

SQLiteCommand cmd = _connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM MyTable WHERE MyColumn = @parameter";
cmd.Parameters.Add( new SQLiteParameter( "@parameter", textfield ) );
SQLiteDataReader reader = cmd.ExecuteReader();

Using a parametrised SQL will escape all input values and help protect you from SQL injection attacks.

bstoney
  • 6,594
  • 5
  • 44
  • 51
  • What do i do if i have multiple params? do i write cmd.CommandText = "insert ... @parameter ... @parameter"; cmd.Parameters.Add(...);cmd.Parameters.Add(...); ? –  Mar 11 '09 at 03:53
  • Yes, there should also be an AddRange method available where you can pass an array of params that match the params in the sql statemenet. – Quintin Robinson Mar 11 '09 at 03:59
  • You can call Parameters.Add multiple times just as you have asked or use the Parameters.AddRange which accepts an array of parameters. – bstoney Mar 11 '09 at 04:07
1

You can also replace all single quote delimiters with doubt single quotes (not ").

sql = sql.Replace("'","''");
klkitchens
  • 1,202
  • 2
  • 16
  • 39