SQL Escape Sequences
-
I'm constructing a search string based on input from user in a web application. This search string basically translates to a search criteria and is assigned to a RowFilter on a dataview. My code throws an exception when my search strings contains quotes and other SQL characters which might invalidate the search criteria. How do I make sure that this doesnt happen, ie. how to avoid sql escape characters from my search string. Is there a method in .net which will help me. I tried google, searching for SQL escape characters but I couldnt find anything relevant to what i'm looking for. Also note that the filtering is done on a dataview which is already on cache. Thanks, Kannan
-
I'm constructing a search string based on input from user in a web application. This search string basically translates to a search criteria and is assigned to a RowFilter on a dataview. My code throws an exception when my search strings contains quotes and other SQL characters which might invalidate the search criteria. How do I make sure that this doesnt happen, ie. how to avoid sql escape characters from my search string. Is there a method in .net which will help me. I tried google, searching for SQL escape characters but I couldnt find anything relevant to what i'm looking for. Also note that the filtering is done on a dataview which is already on cache. Thanks, Kannan
-
I'm constructing a search string based on input from user in a web application. This search string basically translates to a search criteria and is assigned to a RowFilter on a dataview. My code throws an exception when my search strings contains quotes and other SQL characters which might invalidate the search criteria. How do I make sure that this doesnt happen, ie. how to avoid sql escape characters from my search string. Is there a method in .net which will help me. I tried google, searching for SQL escape characters but I couldnt find anything relevant to what i'm looking for. Also note that the filtering is done on a dataview which is already on cache. Thanks, Kannan
I have this method in one of my utility classes:
public static string ToSQL(object value)
{
if (value == null)
return "NULL";if (value is string) return "'" + value.ToString().Replace("'", "''") + "'"; return value.ToString();}
It's handy for SQL search criteria. Regards, Alvaro
Give a man a fish, he owes you one fish. Teach a man to fish, you give up your monopoly on fisheries.
-
I'm constructing a search string based on input from user in a web application. This search string basically translates to a search criteria and is assigned to a RowFilter on a dataview. My code throws an exception when my search strings contains quotes and other SQL characters which might invalidate the search criteria. How do I make sure that this doesnt happen, ie. how to avoid sql escape characters from my search string. Is there a method in .net which will help me. I tried google, searching for SQL escape characters but I couldnt find anything relevant to what i'm looking for. Also note that the filtering is done on a dataview which is already on cache. Thanks, Kannan
The easiest way to handle this is used parameterized SQL expressions in ADO.NET. For example, if you use a
SqlCommand, use theSqlParameters in theSqlCommand.Parameters(the property documentation includes an example). Constructing the SQL string without the parameters is straight forward enough since you have total control over it. Using parameters for the values (for an INSERT, UPDATE, DELETE, etc.) will eliminate the common problems (trying to inject another statement) while taking care of escaping quotes for you. This is by far a better and safer alternative. The days of concatenating strings to form a SQL expression are (or at least should be) over.Microsoft MVP, Visual C# My Articles
-
The easiest way to handle this is used parameterized SQL expressions in ADO.NET. For example, if you use a
SqlCommand, use theSqlParameters in theSqlCommand.Parameters(the property documentation includes an example). Constructing the SQL string without the parameters is straight forward enough since you have total control over it. Using parameters for the values (for an INSERT, UPDATE, DELETE, etc.) will eliminate the common problems (trying to inject another statement) while taking care of escaping quotes for you. This is by far a better and safer alternative. The days of concatenating strings to form a SQL expression are (or at least should be) over.Microsoft MVP, Visual C# My Articles
Thanks for the tip, I was aware of this, but was wondering how one could use them for search criteria as that of a RowFilter which expects a SQL string. Thanks, Kannan
-
Thanks for the tip, I was aware of this, but was wondering how one could use them for search criteria as that of a RowFilter which expects a SQL string. Thanks, Kannan
You're on your own, there. There is a good side, though: users can't cause an injection of an invalid SQL statement in the
RowFilterproperty, thus - potentially - whiping out your entire database. AnExceptionwould be thrown if anything is invalid. So, while there's nothing really to help you with that (just good ol' string parsing), there's not much harm in not checking for every possible exploit.Microsoft MVP, Visual C# My Articles
-
I'm constructing a search string based on input from user in a web application. This search string basically translates to a search criteria and is assigned to a RowFilter on a dataview. My code throws an exception when my search strings contains quotes and other SQL characters which might invalidate the search criteria. How do I make sure that this doesnt happen, ie. how to avoid sql escape characters from my search string. Is there a method in .net which will help me. I tried google, searching for SQL escape characters but I couldnt find anything relevant to what i'm looking for. Also note that the filtering is done on a dataview which is already on cache. Thanks, Kannan
I have developed following escape function that work fine with gridview rowfilter: public static string Escape(string aString) { string searchStr = Regex.Replace(aString, "[.']", "''"); searchStr = Regex.Replace(searchStr, "[.]]", "[]]"); searchStr = Regex.Replace(searchStr, "[.[]", "[[]"); searchStr = Regex.Replace(searchStr, "[.*]", "[*]"); searchStr = Regex.Replace(searchStr, "[.%]", "[%]"); return searchStr; } Before passing string to rowfilter just escape your search string with above function. Should work:thumbsup: