SQL STATEMENT STRING PROBLEM

[kissarmi]

Simply:
I'm am building an SQL Statement string to populate a recordset in my VB. String looks something like this:

recordset.Open "SELECT * FROM Account WHERE LastName Like '%" & strName & "%' ORDER BY LastName"

I need to know how to handle this if the user enters some character, like a single or double quote, into the strName.
(Maybe, looking for a name like O'Neal).

Thanks.

 

[bjd4jc]

Check out the command Object.

It is also possible to double-quote your strings.

For instance:

strName = Replace(strName, "'", "''")

This is a less secure option that has been discussed many times here on this forum. Do a keyword search on SQL Injection.

 

[DrJavaJoe]

See faq709-1526


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[BobRodes]

You can also use the instr function to check for and disallow double quotes, and also use it to check for apostrophes and add a second one if found. Two single quotes means to SQL server to take the quote literally. In other words "where myfield = 'O''Brien'" will find the string "O'Brien" in myfield.

HTH

Bob

 

[toon10]

Yeah, I use

MyString = Replace(TheStr, "'", "''")

on an import routine and that handles the O'Brians and the likes no problems!

 

[tsdragon]

You really should put code like that inside TGML code tags. It makes it MUCH easier to read:

MyString = Replace(TheStr, "'", "''")

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[BobRodes]

Yeah, just use the replace function for the single quotes, no need to check for them with the instr.


Bob

 

[shannanl]

I set the form's keypreview to true and add this code in the form's keypress event.

'-- MAKE SURE ONLY VALID CHARACTERS ARE ENTERED
If KeyAscii = 34 Then KeyAscii = 96
If KeyAscii = 39 Then KeyAscii = 96
If Chr(KeyAscii) >= "a" And Chr(KeyAscii) <= "z" Then
KeyAscii = KeyAscii - 32
End If

It traps the offending characters and I also use it to convert all characters to uppercase (last item).

Shannan

 

[fredericofonseca]

Shannan,


Preventing the user from entering a perfectly valid character on one's own name is very annoying, and there is no need to prevent it other from being lazy with coding.


As all other posters mentioned you can replace them, and by looking at the faq mentioned you find a even better way of dealing with the problem.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

 If Chr(KeyAscii) >= "a" And Chr(KeyAscii) <= "z" Then
        KeyAscii = KeyAscii - 32
    End If

This is more concise:

keyascii = ucase(keyascii)

Well, I'd say that's a little strong, Frederico. There are applications that disallow the apostrophe for reasons other than laziness, one of which is simply development overhead, for example bulk mailing applications. On the other hand, I do agree with the spirit of what Frederico is saying. It's a good idea to support apostrophes most of the time, especially if the user base is customers.

A good way to support apostrophes is to change your strName variable before plugging it into your select statement, doubling any that you find. If you're not sure how to do this, read up on instr and mid.

Bob

 

[tsdragon]

Skip the instr and the mid, just use replace, like we indicated above.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[DrJavaJoe]

I would like to recommend again to see FAQ709-1526.


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[BobRodes]

Ooops...thanks tsdragon. Old habits die hard. Of course, Tracy's quite right.

Bob

 

[bjd4jc]

I would second the recommendation to again see FAQ709-1526

 

[BobRodes]

Great faq!! Thanks for pointing it out.

Bob