Click here to Skip to main content
15,350,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm Developing a project where I need to execute a query.

My query is like this
SQL
select * from SalesDetails where ItemCode in(" + items + ") and  dealer in(" + dealers + ")"


I'm writing this query using ADO.NET(C#).
Here 'Items' and 'Dealers' are variables of C#, containing string value.

My problem is if the string is normal then no Issue. But when the string contains symbols like { , [ , - , @ , ~ then the query is not executing.


My Question is how to escape all these characters at the time of using executing.

I don't want to replace characters one by another. I need something that will work as @ symbol in C# for escaping all the characters in the string.
Posted

Few link which helps you escape regular special characters[^]

It is highly recommended to use Parameters as advised by the legends
   
Even more important would be guarding from something like

") Drop table SalesDetails --"


You will want to consider using parameters. See http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx[^]. This will also help out when you have special characters in your strings as you are wanting to deal with.

Brent
   
v2
Comments
Sergey Alexandrovich Kryukov 27-Mar-12 16:35pm
   
Correct, my 5.
I added description of other problems, most importantly, the SQL Injection exploit, please see my answer.
--SA
[no name] 28-Mar-12 2:11am
   
thank you for the reply.
But my situation doesn't allow me to apply sqlparameters in query.
Because i have a string given in my question. I need to execute that query stored by the string and need to use the string as query in some other page.
If possible suggest me any other option.
dbrenth 28-Mar-12 8:37am
   
The other option is redesign. You can't afford not to use parameters. You owe it to your client to deliver a product that's safe.

I recommend building your query into stored procedures. Stored procs can take comma delimited strings as an argument. And you can pass the argument in as a parameter. Or you can generate the sqlcommand dynamically as ... ItemCode = ? or ItemCode = ? or ItemCode = ? ...

If you stick with what you have, the special characters will be the least of your problems.
[no name] 28-Mar-12 12:53pm
   
My customer have more than 1000 items. so ItemCode = ? or ItemCode = ? or ItemCode = ? ... will not work. isn't?
Store Procedures is a good idea. But I am using Attached DB File.
So Its another problem.
I'm not able to implement lots of SQL Server features.
There is no option in SQL where I can have something like @ in c# to escape all the symbols in a string.
The advice by dbrenth is correct. I would like to add the warning way more important than character input: failure to use parametrized statements make the application very unsafe.

With the query string composed of some data, you can get some strings from, say, user input; and it can be anything; for example… SQL code. This way, your application is vulnerable to a well-known exploit called SQL Injection. Please see:
http://en.wikipedia.org/wiki/SQL_injection[^].

Read about the importance of parametrized statements. Except for safety, they are important for performance and consistence as they are types, in contrast to strings.

By the way, you also should not use repeated string concatenation ('+') for performance reasons; because strings are immutable; System.Text.StringBuilder and string.Format are free from this problem.

—SA
   
Comments
[no name] 28-Mar-12 2:12am
   
thanks for the valuable suggestion.
Sergey Alexandrovich Kryukov 28-Mar-12 20:33pm
   
My pleasure.
If you agree it's valuable, please consider accepting this answer formally (green button) -- thinks.
--SA
[no name] 29-Mar-12 4:38am
   
Its a good suggestion, but not the solve of my question. So sorry.
Sergey Alexandrovich Kryukov 29-Mar-12 10:46am
   
No, I'm sorry I did not address your problem. Nevertheless, parametrized command is a must.
--SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900