April 26, 2005 at 9:33 am
I am trying to perform a SQL query on a customer database with apostrophe's in the customers last name. SQL will not let me do this because it is seeing the apostrophe as a close quote and ignoring the rest of the name. How can I get SQL to read the entire name (apostrophe included) so that I can execute the query?
Thanks for any help you can give.
April 26, 2005 at 9:36 am
make the quote into two single quotes something like this:
select 'O''Malley'
or programatically: REPLACE(col, '''', '''''')
dl
April 26, 2005 at 9:38 am
Here is is another example using single quotes:
select
from
where name = 'john doe''s'
April 27, 2005 at 2:01 am
April 27, 2005 at 2:42 am
For the sake of completeness, another way to do it programmatically which isn't mentioned in this thread or the one referred to by Stewart, is to use the ASCII code for double quotes when building the string
eg in VB
'code to search for client by name
dim sql as string, cl as string
cl = "O'Mally" 'normally this would be entered by the user
sql = "SELECT * FROM tblname WHERE clientname = " & chr(34) & cl & chr(34)
pg
April 27, 2005 at 2:47 am
I would emphasise, for all the reasons set out in the referenced threads, please avoid dynamic SQL whenever possible. In particular, allowing users to enter anything directly into a query is opening a massive hole in your security.
April 27, 2005 at 3:14 am
Absolutely - no doubt sp's are the preferred way to go. Especially for web enabled apps, or any other scenario where you can't trust the users not to attempt a sql injection attack. (Most places I've worked, though, you had to trust the users to enter the right data anyway; and if a user wanted to wreck something more dramatically, they didn't need to know SQL - they could just walk into the next room and give the server a good kicking 🙁 . . so maybe I've become a bit blase about the more esoteric kinds of security aspects!)
pg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply