NOOB Question: using LIKE in SelectParameter
-
Been doing C# for years - but this is the first time I've needed to do a web app so I'm trying ASP.NET for the first time. I have a listview bound to a SqlDataSource. My SelectCommand needs to be something like "SELECT .... FROM ... WHERE Field LIKE '%value%' I have ControlParameter tied to a textbox that will be the value used in the LIKE clause. Here is my question: If I code my SelectCommand to look like ......WHERE Field LIKE '%' + @value + '%' It works, but if somebody types a value like O'Hara into the text box - it will choke due to the concatenation (and I'm pretty sure open me up to SQL injection attacks) If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site. So what is the best place to concatenate the % signs at the start and end of my search string? This must be a VERY VERY COMMON thing to do. What are others doing about this?
-
Been doing C# for years - but this is the first time I've needed to do a web app so I'm trying ASP.NET for the first time. I have a listview bound to a SqlDataSource. My SelectCommand needs to be something like "SELECT .... FROM ... WHERE Field LIKE '%value%' I have ControlParameter tied to a textbox that will be the value used in the LIKE clause. Here is my question: If I code my SelectCommand to look like ......WHERE Field LIKE '%' + @value + '%' It works, but if somebody types a value like O'Hara into the text box - it will choke due to the concatenation (and I'm pretty sure open me up to SQL injection attacks) If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site. So what is the best place to concatenate the % signs at the start and end of my search string? This must be a VERY VERY COMMON thing to do. What are others doing about this?
Figmo2 wrote:
I have ControlParameter tied to a textbox
Instead, tie parameter to a string variable. Set the string variable to the value of the text box, and then replace every singe ' with '' (ie escape out the single quotes in the input criteria. You can thyen use
WHERE Field LIKE '%' + @value + '%'without exposing yourself to any injection attacks. -
Been doing C# for years - but this is the first time I've needed to do a web app so I'm trying ASP.NET for the first time. I have a listview bound to a SqlDataSource. My SelectCommand needs to be something like "SELECT .... FROM ... WHERE Field LIKE '%value%' I have ControlParameter tied to a textbox that will be the value used in the LIKE clause. Here is my question: If I code my SelectCommand to look like ......WHERE Field LIKE '%' + @value + '%' It works, but if somebody types a value like O'Hara into the text box - it will choke due to the concatenation (and I'm pretty sure open me up to SQL injection attacks) If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site. So what is the best place to concatenate the % signs at the start and end of my search string? This must be a VERY VERY COMMON thing to do. What are others doing about this?
Check this article out ... http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx[^] I think this is the way you want to go ... Keep asking questions ... its the only way to learn. :cool:
-
Been doing C# for years - but this is the first time I've needed to do a web app so I'm trying ASP.NET for the first time. I have a listview bound to a SqlDataSource. My SelectCommand needs to be something like "SELECT .... FROM ... WHERE Field LIKE '%value%' I have ControlParameter tied to a textbox that will be the value used in the LIKE clause. Here is my question: If I code my SelectCommand to look like ......WHERE Field LIKE '%' + @value + '%' It works, but if somebody types a value like O'Hara into the text box - it will choke due to the concatenation (and I'm pretty sure open me up to SQL injection attacks) If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site. So what is the best place to concatenate the % signs at the start and end of my search string? This must be a VERY VERY COMMON thing to do. What are others doing about this?
Figmo2 wrote:
If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site.
I think this is correct functionality, it is looking for records where Field contains "%....%" rather than "....". I don't see the need to manually type the "%" in the majority of cases, it'll just confuse most [non-technical] users. Do you need to use wildcards?
-
Been doing C# for years - but this is the first time I've needed to do a web app so I'm trying ASP.NET for the first time. I have a listview bound to a SqlDataSource. My SelectCommand needs to be something like "SELECT .... FROM ... WHERE Field LIKE '%value%' I have ControlParameter tied to a textbox that will be the value used in the LIKE clause. Here is my question: If I code my SelectCommand to look like ......WHERE Field LIKE '%' + @value + '%' It works, but if somebody types a value like O'Hara into the text box - it will choke due to the concatenation (and I'm pretty sure open me up to SQL injection attacks) If I code my SelectCommand to look like ......WHERE Field LIKE @value It will work perfectly if I manually type the % signs in the text box for value. Including if I type %O'Hara% (the single quote no longer chokes it since I'm not concatenating anymore) - but obviously I don't want to have to teach the world to always put % sings at the start and end of their search strings on my web site. So what is the best place to concatenate the % signs at the start and end of my search string? This must be a VERY VERY COMMON thing to do. What are others doing about this?
Great suggestions all - thank you. What I ended up doing was a little different. I think elegant, but maybe not. Open to critiques... I left the ControlParameter tied to my text box. (the parameter is called @SearchExpr) My WHERE clause in the SelectCommand is simply "...WHERE Field LIKE @SearchExpr..." (no concatenation, thus no problems with single quotes needing to be escaped) And added an event handler for SqlDataSource.Selecting that does this...
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@SearchExpr"].Value = "%" + e.Command.Parameters["@SearchExpr"].Value + "%"; }So now the user just enters ANY search string into the text box (e.g. "O'Hara") This is passed to the SelectCommand as a parameter, thus it is not executable code - so no worries about SQL injection (I think) And then, in the event handler, right before the SelectCommand is applied to the SqlDataSource, I modify the value to add the % signs front and back. It works like a charm but the only thing I am not certain of is how well protected I am against injection attacks. Am I right in assuming that this should be adequate protection? -
Great suggestions all - thank you. What I ended up doing was a little different. I think elegant, but maybe not. Open to critiques... I left the ControlParameter tied to my text box. (the parameter is called @SearchExpr) My WHERE clause in the SelectCommand is simply "...WHERE Field LIKE @SearchExpr..." (no concatenation, thus no problems with single quotes needing to be escaped) And added an event handler for SqlDataSource.Selecting that does this...
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@SearchExpr"].Value = "%" + e.Command.Parameters["@SearchExpr"].Value + "%"; }So now the user just enters ANY search string into the text box (e.g. "O'Hara") This is passed to the SelectCommand as a parameter, thus it is not executable code - so no worries about SQL injection (I think) And then, in the event handler, right before the SelectCommand is applied to the SqlDataSource, I modify the value to add the % signs front and back. It works like a charm but the only thing I am not certain of is how well protected I am against injection attacks. Am I right in assuming that this should be adequate protection?Figmo2 wrote:
Am I right in assuming that this should be adequate protection?
You're using a parameterised query, so you should be OK on that front. However, take a look at http://msdn.microsoft.com/en-us/library/ms179859.aspx[^] for some more "magic characters" that might appear in your search string, and give unexpected results.