Prevent sql injection by removing characters and other possible ways

Question

SQL injection can be avoided if I remove "'" character in the variables of a sql query. The sql query that I use is:

dbCommand = new OleDbCommand("update Table1 set PhoneNo = '" + phone + "' where Table1.Company = '" + company + "'", dbConnection);
dbCommand.ExecuteNonQuery();

I also use SELECT sql query in a similar way:

dbReader = new OleDbCommand("select * from Table1 where Table1.Company = '" + company + "'", dbConnection).ExecuteReader();
dbReader.Read();

if (dbReader.HasRows)
{
    //Do operations using dbReader["Company"]
}

Are there any other characters in the variables that can cause SQL injection or other risks? I can remove those. What are other ways of preventing SQL injection or other risks?

Answer 1

The best way is simply to use parametrized queries and don't try and remove any '. One example would be this:

dbCommand = new OleDbCommand("update Table1 set PhoneNo = ? where Table1.Company =? ", dbConnection);

dbCommand.Parameters.Add(phone);
dbCommand.Parameters.Add(company);

The OleDbCommand class allows you to specify parameters by name also, according to MSDN.

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparametercollection.aspx

So you could replace those ? in my example, with actual parameter names like so:

dbCommand = new OleDbCommand("update Table1 set PhoneNo = @phone where Table1.Company =@company ", dbConnection);

dbCommand.Parameters.Add("@phone",phone);
dbCommand.Parameters.Add("@company",company);

Update (Comment from Steve)

You could use a name for the parameter, but it is ignored by OleDb. If you change the order in which you add the parameter the query doesn't work – Steve