c# textbox characters that can cause my SQL query to stop working

Question

I have a textbox in my WinForm.

The user types text (anything that their keyboard allows). When they click save, sometimes an error message appears with

Syntax Error (missing operator) in query expression

I know that certain characters like ' are breaking my SQL query

I have a few questions

Is there anything I can do so that my Query doesn't break and allow users to type whatever they want?
If there isnt a way to do #1, how can I scan the text and remove all characters that would break my Query?

Similar question http://stackoverflow.com/questions/249567/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-sharp-code — Orhan Cinar, Apr 25 '12 at 14:32

score 5 · Accepted Answer · answered Apr 25 '12 at 14:25

5

try passing the textbox value to the query by using parameters. This will also prevent SQL-injection attacks.

should look something like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * from TableName WHERE FieldA = @FieldValue";
cmd.CommandType = CommandType.Text;

//Define SqlParameter object and assign value to the parameter
cmd.Parameters.Add("@FieldValue", SqlDbType.VarChar, 50);
cmd.Parameters["@FieldValue"].Value = TextBox.Value;

answered Apr 25 '12 at 14:25

amaters

2,266
2
24
44

Using SQL Parameters, I can have ' " , and other keyboard characters? – Cocoa Dev Apr 25 '12 at 15:05
If i do this, I'll need to make major changes in my code. So do you have an example of this with OleDbCommand (from connection string to the Insert query) – Cocoa Dev Apr 25 '12 at 15:16
@CocoaDev, it works identically to SqlCommand. Your changes should be trivial. – Brad Rem Apr 25 '12 at 15:29
Do I need to do anything after the Parameters for my Insert? – Cocoa Dev Apr 26 '12 at 16:06
is this cmd.Parameters["@FieldValue"].Value = TextBox.Value; correct to retreive data from an SQL query? – Cocoa Dev Apr 27 '12 at 16:26
That line supplies the parameter with its value (from the textbox in this case) – amaters Apr 27 '12 at 20:53

score 2 · Answer 2 · answered Apr 25 '12 at 14:25

I know that certain characters like ' are breaking my SQL query

It sounds like your using string concatenation to add your parameters if this is case.

Use SQL Parameters instead and this should solve your problem.

Also its worth noting, building SQL queries with string concatenation is prone to SQL Injection.

score 0 · Answer 3 · answered Apr 25 '12 at 14:29

0

A better way is to use parametrized queries. See Adding Parameters to Commands. This does not only solve your bad character problem but also removes the danger of SQL-injection (Wikipedia).
If you still prefer to use string concatenation then replacing single quotes by two single quotes should be safe enough.

string sql = "SELECT * FROM tbl WHERE Name = '" + name.Replace("'","''") + "'";

answered Apr 25 '12 at 14:29

Olivier Jacot-Descombes

104,806
13
138
188

Option 2 is still prone to SQL Injection, and would not be safe. – Curtis Apr 25 '12 at 15:06
@Curt: Can you show me an example of a text entry that would inject malicious code for option 2? I don't think it's possible. – Olivier Jacot-Descombes Apr 25 '12 at 21:32

c# textbox characters that can cause my SQL query to stop working

3 Answers3