Solved

Quotes and double quotes in SQL Statement

Posted on 2006-06-23
3
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.

738 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