how to use the keyword 'LIKE' in SQL query in C# code, what will be the sentax of C# statement
-
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
A few things: 1) Please spell-check the post before you submit. A continuous broken language embarrasses the readers. 2) A continuos all-caps also indicates yelling at the users. 3) Technically, your query is vulnerable for SQL Injection attacks. You may need to review your SQL Querying patterns. I would also advise you to have a read of Forum Posting Guidelines at http://www.codeproject.com/kb/scrapbook/forumguidelines.aspx [^]
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson -
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
You should be using parameters to pass filterable stuff to the query. Also, there is absolutely no validation on the Text value in crimewithdate Where cmd is your SqlCommand object:
string value = string.Concat('%', crimewithdate.Text, '%');
cmd.CommandText = "SELECT b_name, b_desc "+
"FROM collapsed_building "+
"WHERE b_name LIKE @name";
cmd.Parameters.AddWithValue("@name", value);Now, how big is your b_name column? Before you do anything with your query you should ensure that crimewithdate.Text does not exceed that size. Are there any other constraints? (e.g. only permitted to have alpha-numeric characters? Check for those also)
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
J4amieC wrote:
(LIKE '%" + crimewithdate.text2 + "%')
Don't encourage SQL Injection Attackable code, please.
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
I didnt encourage it, I simply corrected his mistake. Frankly if the OP has this wrong then he's going to be bafffled by terms that he doesnt understand. Im 100% with you on the Sql Injection thing, but there is learning to walk before one can run.
J4amieC wrote:
there is learning to walk before one can run.
I teach a basic C# training course. I actually threw out the materials I'd been given on the subject of databases and rewrote it. I ensure that at no time is anyone encouraged to inject values and I go directly to parameterised queries. So far with good results. However, you might be right. If they already have the bad habit it might make it more difficult to break.
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
half-life wrote:
" WHERE b_name = '" + crimewithdate.text2 + "' ";
Please don't encourage SQL Injection Attackable code.
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
Colin Angus Mackay wrote:
Please don't encourage SQL Injection Attackable code.
Can u elaborate please? i decided not to take "Athics and Hacking" course and instead i took "advanced algorithms" in university :) :)
Have Fun Never forget it
The OP supplied code that was (if it worked) susceptable to a SQL Injection Attack. Your "correction" is also susceptable to a SQL Injection Attack. For an article on what they are and how to prevent SQL Injection Attacks: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx[^]
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
The OP supplied code that was (if it worked) susceptable to a SQL Injection Attack. Your "correction" is also susceptable to a SQL Injection Attack. For an article on what they are and how to prevent SQL Injection Attacks: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx[^]
Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog
-
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
My 2 cents: you should never prepend with % or + in LIKE statement, it turns off indexes. It could hurt performance if you have lots of entries.
----- You seem eager to impose your preference of preventing others from imposing their preferences on others. -- Red Stateler, Master of Circular Reasoning and other fallacies If atheism is a religion, then not collecting stamps is a hobby. -- Unknown God is the only being who, to rule, does not need to exist. -- Charles Baudelaire