DataTable DataRow Select String with Quotation Marks

Question

My string include quotation mark; the select statement crash.

vm_TEXT_string = "Hello 'French' People";
vm_DataTable_SELECT_string = "[MyField] = '" + vm_TEXT_string + "'";
DataRow[] o_DataRow_ARRAY_Found = vco_DataTable.Select (vm_DataTable_SELECT_string);

I cannot use this statement: string filter = "[MyColumn]" + " LIKE '%" + SearchWord + "%'";

I found string format:

DataRow[] oDataRow = oDataSet.Tables["HasDiseas"].Select ( string.Format ( "DName='{0}'", DiseasListBox.SelectedItem.ToString () ) );

Any suggestion to selecta string with quotation mark?

Thank you, Rune

Answer 1

For a datatable, you can replace the single quotation mark with two quotation marks:

string.Format("DName='{0}'", DiseasListBox.SelectedItem.ToString().Replace("'", "''")

But keep in mind that you should not do this with actual sql queries. It's possible for crackers to abuse that technique to send undesirable queries to your database.

Another option is to do something like this:

IEnumerable<DataRow> rows = oDataSet.Tables["HasDiseas"].Where(r => r["DName"] == DiseasListBox.SelectedItem.ToString());

Answer 2

This depends on your database engine, but generally, you can escape the single quote (') with two single quotes ('').

Although, the best way to do it is to use a parametrized query, which will do the special character escaping for you.