Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: dotnetnuke
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 6-Mar-12 6:20am
uvasani326
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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. Smile | :)
 
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
  Permalink  
v3
Comments
Naerling at 6-Mar-12 14:13pm
   
My 5! To bad your answer kind of disappears in all the (deleted) spam in here :(
SAKryukov at 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
Naerling at 6-Mar-12 14:41pm
   
I didn't mean it got deleted, it's just hard to find in so many spam messages :)
SAKryukov at 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
Naerling at 6-Mar-12 18:04pm
   
Yep, I've already reported a few spammers there :)
uvasani at 6-Mar-12 14:21pm
   
Hi
 
Can you please correct my code ?
 
Search Clause: (LastName LIKE '%[Search2:Text]%')
 
Thank you
SAKryukov at 6-Mar-12 14:35pm
   
Parametrized query parameter format depends both on your database and database provider you use. The syntax is different, it could be positional of named. Please see the page I referenced, or the documentation of the database provider you are using or dotnetnuke documentation.
--SA
CPallini at 6-Mar-12 16:19pm
   
My 5++.
SAKryukov at 6-Mar-12 17:08pm
   
Thank you.
--SA
digimanus at 7-Mar-12 15:36pm
   
this question lead like yesterday to a spam answer!!! (Solution 9)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 10

One option is to replace the single quote with two single quotes, similar to this:
 
replace(FIELD, "'", "''")
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 7 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100