Click here to Skip to main content
13,097,230 members (68,157 online)
Rate this:
Please Sign up or sign in to vote.
See more:

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 <> '') AND (WindowsEmailAddress <> '') 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
Rate this: bad
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:[^], or after all:[^].

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:[^].

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:[^].

Naerling 6-Mar-12 14:13pm
My 5! To bad your answer kind of disappears in all the (deleted) spam in here :(
SAKryukov 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.
Naerling 6-Mar-12 14:41pm
I didn't mean it got deleted, it's just hard to find in so many spam messages :)
SAKryukov 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
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.

Naerling 6-Mar-12 18:04pm
Yep, I've already reported a few spammers there :)
uvasani 6-Mar-12 14:21pm

Can you please correct my code ?

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

Thank you
SAKryukov 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.
CPallini 6-Mar-12 16:19pm
My 5++.
SAKryukov 6-Mar-12 17:08pm
Thank you.
digimanus 7-Mar-12 15:36pm
this question lead like yesterday to a spam answer!!! (Solution 9)
Rate this: bad
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, "'", "''")

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.170813.1 | Last Updated 7 Mar 2012
Copyright © CodeProject, 1999-2017
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