Solved

Quotes and double quotes in SQL Statement

Posted on 2006-06-23
3
344 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tviruailstringtree sort multi columns on header click 1 54
When i run adoquery my application freezes 26 152
Base1 Encode/Decode 3 68
Advice in Xamarin 21 57
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

863 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

26 Experts available now in Live!

Get 1:1 Help Now