Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am facing a probleam in SQLGridSelectedView from dotnetnuke, If I type O'Connell in my LastName Textbox I coudn't get the detail but if I type Connell than I'll get all the details.

For Example :
First Name = Daniel
Last Name = O'Connell


SQL Statement(SELECT or EXEC Only): SELECT LastName AS [Last Name], FirstName AS [First Name],Office AS Location, Department, Phone, ''+WindowsEmailAddress+'' AS Email FROM crestron_directory WHERE (LastName IS NOT NULL AND LastName <> '') AND (FirstName IS NOT NULL AND FirstName <> '') AND (((WindowsEmailAddress <> 'gfeldstein@crestron.com') AND (WindowsEmailAddress <> 'lfeldstein@crestron.com') AND (WindowsEmailAddress <> '') AND (WindowsEmailAddress is not null)))


Text Search
Search Clause: (LastName LIKE '%[Search2:Text]%')



I have tried
-(LastName LIKE '% ''[Search2:Text]%')
-(LastName LIKE '% '''[Search2:Text]%')
-(LastName LIKE '% \'[Search2:Text]%')
-(LastName LIKE '% ''[Search2:Text]%')
-(LastName LIKE '%" + [Search2:Text] + "%')
-(LastName LIKE '%" + [Search2:Text] + "%'

")


Thank you

Posted

You could easily find out how to escape the apostrophe and other things, for example:
http://www.sqlteam.com/article/apostrophes-and-quotation-marks-in-sql-server[^], or after all:
http://bit.ly/x5qYJi[^].

This is almost irrelevant though. You should think about very different thing: how come such text as "O'Connel" can get into your query? I can tell you: it should never appear in a query. What, do you hard-code a person's name in the source code? No? Then you probably compose a command string from during run time, probably even from interactive user input.

You should never do this. You need to use parametrized queries. Please see:
http://msdn.microsoft.com/en-us/library/ms254953.aspx[^].

If you use parametrize queries, the problem of the apostrophe won't even come into consideration: you assign actual values to the parameters, which are types. In the case of string, you supply a string value as it is. Even with a null character inside. :-)

If my arguments are not yet convincing to you, think about the security: composing the text of the query from the input is simply prohibitively dangerous. Please read about the danger of SQL Injection and the role of parametrized statements:
http://en.wikipedia.org/wiki/SQL_injection[^].

—SA
 
Share this answer
 
v3
Comments
Sander Rossel 6-Mar-12 14:13pm    
My 5! To bad your answer kind of disappears in all the (deleted) spam in here :(
Sergey Alexandrovich Kryukov 6-Mar-12 14:31pm    
Thank you, Naerling.
Yes, some of my answers disappeared when they were on the page later removed, classified as "non a question", but which ones do you mean? I did no noticed many such cases lately.
--SA
Sander Rossel 6-Mar-12 14:41pm    
I didn't mean it got deleted, it's just hard to find in so many spam messages :)
Sergey Alexandrovich Kryukov 6-Mar-12 17:16pm    
Good, thank you.

I replace the text, just in order to shorted the page even when the bad post is deleted (see above). I also report it to recently introduced forum http://www.codeproject.com/Forums/1652005/Spam-Watch.aspx.
I recommend everyone to do so (I know that I do).

I remembers well: enemy's bombеrs and fighter are best destroying at the airports; after they are launched fighting them is way more difficult.

--SA
Sander Rossel 6-Mar-12 18:04pm    
Yep, I've already reported a few spammers there :)
One option is to replace the single quote with two single quotes, similar to this:

VB
replace(FIELD, "'", "''")
 
Share this answer
 

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