Solved

Quotes and double quotes in SQL Statement

Posted on 2006-06-23
3
346 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 125 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:ZaDDaZ
ZaDDaZ earned 125 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now