Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

Quotes and double quotes in SQL Statement

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
jskfan
Asked:
jskfan
  • 2
2 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
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
 
Dan RuehleSoftware EngineerCommented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now