how to use the keyword LIKE in the SQL query in C# statement

mavii

i am using ASP.net, C# anD SQL Server2005,in the following query: string q = "SELECT collapsed_building.b_name,collapsed_building.b_desc FROM collapsed_building WHERE collapsed_building.b_name LIKE '" + crimewithdate.text2 + "' "; I WANT TO USE % AFTER THE KEYWORD like SO THAT ALL THE BUILDING NAMES WHICH MATCH THE VALUE ENTERED BY THE USER ARE DISPLAYED WHEN I WRITE (LIKE '" + %crimewithdate.text2 %+ "'), IT GIVES ERROR, WHAT WILL BE THE CORRECT SENTAX

Pete OHanlon

Please, don't do this. You leave yourself with open to a Sql Injection attack. Take a look at this[^] article on how to avoid this. Now, you many want to consider using something like this:

DECLARE @building_name NVARCHAR(100)
SELECT @building_name = 'Test'
-- The lines above are just a sample to get you started. Move this into a stored procedure and use
-- the code below to do the actual work. Note that you would want to determine whether or not
-- @building_name ended in a % before using this code.
SELECT @building_name = @building_name + '%'

SELECT collapsed_building.b_name,collapsed_building.b_desc FROM collapsed_building WHERE collapsed_building.b_name LIKE @building_name

Deja View - the feeling that you've seen this post before.

My blog | My articles

Michael Potter

Pete is correct in saying that this is dangerous code. You have to use strong protection to make sure that crimewithdate.text2 does not contain any malicious SQL code instead of the expected search parameter. To answer your question as asked, here is one solution:

string q = string.Format("SELECT collapsed_building.b_name,
collapsed_building.b_desc FROM collapsed_building 
WHERE collapsed_building.b_name LIKE '%{0}%'", crimewithdate.text2);

(The line breaks are for readability only) Do realize that an evil user could enter something like; x'; TRUNCATE TABLE collapsed_building; SELECT * FROM collapsed_building WHERE b_name LIKE 'x and wreck your whole day. It is much better to pass crimewithdate.text2 as a parameter and not expose your database to bad people.

modified on Tuesday, January 22, 2008 11:26:49 AM