Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Quotes and double quotes in SQL Statement

Posted on 2006-06-23
3
Medium Priority
?
355 Views
Last Modified: 2010-04-17
Can someone explain to me the usage of quotes and double quotes in this statement:

cmdcustomers.commandtext="Update Customers set" _
& "LastName= ' " & Customer.Lastname & " ' ,Fisrtname= ' "_
& Customer.Firstanem & " '

I know that sql statement needs to be between double quotes,  but I don't know why in some cases they use single quotes and double quotes together.

Thanks
0
Comment
Question by:jskfan
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 375 total points
ID: 16973526
Hi,

If the column referred to (e.g. LastName, or Fisrtname [Firstname?]) is defined as a datatype of text (string), then the value must be enclosed within quotes (either double or single).

Such columns are typically enclosed within double quotes so that any apostrophe (') characters  [i.e. single quotes] in the text are stored correctly.

If single quotes were used & an apostrophe were in the text value, then the end of the quoted text would be terminated early,

e.g.
If the text value for Lastname were O'Donnell...

Update Customers set LastName='O'Donnell', Fisrtname='something'

Then this would fail because LastName would be seen as 'O' and the Donnell' suffix would be considered invalid syntax.

In this case, double quotes would be used...

Update Customers set LastName="O'Donnell", Fisrtname='something'
or
Update Customers set LastName="O'Donnell", Fisrtname="something"


I would suggest that you use double quotes in the CommandText above as no-one should have a First or Last name with a double quote within it; some First names & certainly some Last names encountered may have single quote characters (apostrophes) within them.

BFN,

fp.
0
 
LVL 1

Assisted Solution

by:Dan Ruehle
Dan Ruehle earned 375 total points
ID: 16973776
The typical way of escaping single quotes is to double up the single quotes, not alternate between double or single quotes.  So, the update should be:

update Customers set LastName = 'O''Donnell', Firstname = 'something'

Note, that there are two single quotes between the O and D in the last name.  The easy way to do this is to do a Replace(value, "'", "''") on the variable.

BUT!!!!  This code as is would be a big security hole to SQL injection.  Imagine if someone's last name was:

O'; exec xp_cmdshell 'move c:\windows\*.* c:\windows\*.gotcha';

That would rename all files in your windows folder and really mess up your system (assuming you didn't do the replace thing).  There are plenty of samples of bad stuff people can do via SQL injection and none of them are very pretty.  So, what you should do is:

cmdcustomers.CommandText="Update Customers set LastName= ?, Firstname = ?";
cmdcustomers.Parameters.Add(Customer.Lastname);
cmdcustomers.Parameters.Add(Customer.Firstname);

There is nothing wrong with the replace method, but if you forget it in just one place, you're just asking for it!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16977966
Thanks for closing the question.

Not sure why you graded as 'B' though, as you never responded to seek clarification or ask for further information.

If the above responses were not clear, all you had to do was ask for additional assistance.

BFN,

fp.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Make the most of your online learning experience.
Six Sigma Control Plans
Starting up a Project

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question