how to handle special character within the query..?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello there,

I am working on c#.net and i have used few queries to get data from the database based on the where clause, Now the problem is the value i am passing to the where clause may or may not have special character (eg. ',",/ or coma). how can i dynamically check the value containing these characters to the value in database.

for Example :

C#

string Description= Convert.ToString(dr["Description"]); // dr["Description"] may contain any special characters 
//following line is to check the single quote
Description= Description.Replace("'","''");

string ProductCode= Convert.ToString("SELECT [Code] FROM [Products]  where Description = '" + Description+ "' and [Active] = '1'");

please help me out.

thanks.

Posted 4-Sep-14 10:41am

kashyap10

Add a Solution

Comments

Sergey Alexandrovich Kryukov 4-Sep-14 17:42pm

In which way those characters are "special"? They are just characters, no better and no worse than other ones. :-)
—SA

1 solution

Add a Solution

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

PhilLenoir · Accepted Answer · 2014-09-04T11:01:00

Solution 1

Parameterize your query using a SQL Command object.

C#

using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        using SqlCommand cmd= new SqlCommand(
               "SELECT Code From Products WHERE Description = @description", con);
        {  
            cmd.AddParameterWithValue("@description", Description);
            using SQL DataReader dr = cmd.ExecuteDataReader;
            {
                 // Insert what you want to do with query results here
            }
        }
    }

Parameterization protects from SQL injection AND deals with special characters (in this case, just the apostrophe)

Posted 4-Sep-14 11:01am

PhilLenoir

Comments

Sergey Alexandrovich Kryukov 4-Sep-14 17:46pm

Absolutely. My 5.
I was always wondering why struggling with syntax to escape some characters and making such a big deal out of it, if using parametrized statements requires anyway resolve such problems before they have a chance to appear, no matter what those strings are. :-)
—SA

kashyap10 5-Sep-14 10:51am

Hey thanks for the solution ... but its not working with " (double quote)

PhilLenoir 5-Sep-14 11:00am

It should. Can you give the exact line of code that you are using and a sample of the data causing an issue. What happens (error message, results not returned, ...), i.e., what does "not working" mean. I'm only here for another hour and I'm on vacation next week ....

kashyap10 5-Sep-14 11:32am

thanks for the response ...

i want to match following
Product "C" Long Beach
but its giving me
Product \"C\" Long Beach

so it could not find the value in where clause.

PhilLenoir 5-Sep-14 11:58am

What code are you using to assign the value to your varable "Description"? Something is escaping the double quote. I don't have time to research this for you until I'm back over a week away. In the meantime, I suggest the following:
Try to find out where the escape (\") is coming from - use debug to inspect your variable and where you variable comes from (e.g. the data reader in your fragment). BTW, you don't need "Convert.ToString" if already a string. Try tou understand why this is happening and your solution should present itself.

A quick and dirty fix is to replace the offending escape sequence in the query. I strongly advocate finding the real cause and fixing the problem, but if you are under time pressure you can change the query to:
SELECT Code FROM Products WHERE Description = REPLACE(@Description, CHAR(92)+CHAR(34), CHAR(34))
Using the ascii codes wil certainly circumvent any escape sequence issues.

kashyap10 5-Sep-14 14:09pm

Thanks man ... its working

how to handle special character within the query..?

1 solution

Solution 1

Add your solution here

Preview 0

how to handle special character within the query..?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us