The apostrophe saga in VB and SQL 3

[phzero]

Hi all,

I have these VB statements:
MyProd = "Bread's Butter"
rs.Find "ProductName = '" & MyProd & "'"

Here's the issue. As you can see, MyProd contains an apostrophe, which wreaks havoc in the next statement. I could strip the apostrophe but then it won't find the entry in the database. Does anyone know of a workaround for this. I have run out of options. Any responses are appreciated. Thanks a mill and have a good day.

 

[vbkris]

how did u insert info to ur database?

Known is handfull, Unknown is worldfull

 

[vbkris]

try this:
MyProd = "Bread\'s Butter"
rs.Find "ProductName = '" & MyProd & "'"


Known is handfull, Unknown is worldfull

 

[CCLINT]

Try:

rs.Find "ProductName='" & Replace(MyProd,"'","''&quot & "'"

 

[strongm]

What CCLINT said

 

[phzero]

Hi CCLINT / strongm,

Maybe I should elaborate on the story. I need not be the one to put the data into the database (SQL Server 7.0). I have tried your method but it won't find the entry since the column in the database has the apostrophes in place. Here is my scenario: I read the ProductName column from the database into a listbox. When the user clicks on an entry, the application will fill in the rest of the fields, such as price, a picture and so forth. In the click event of the listbox I then do this:
rs.Find "ProductName = '" & lList.Text & "'"
As you can see, if I replace anything in the sought string, it won't find the entry in the database. This is my dilemma.
Any views will be appreciated. Thanks.

 

[MarkSweetland]

What CCLINT and strongm are suggesting is replacing any single quote in the text with two single quotes. SQL Server uses single quotes as a text delimiter, which normally hoses up search criteria. But when two single quotes are encountered, SQL includes a single literal quote ['] character in the search criteria.





Mark


A molehill man is a pseudo-busy executive who comes to work at 9 AM and finds a molehill on his desk. He has until 5 PM to make this molehill into a mountain. An accomplished molehill man will often have his mountain finished before lunch
- Fred Allen

 

[CasperTFG]

IMO I wouldn't let them put in the single quote. In a DB program it can be more dangerous than you think. Take this example.
If this is your Table
t_Products
-ID
-Products
-Name
-Description
-Price

And you had a search function with Code something like this.
[tt]
Private Sub cmdSearch_Click()

Dim strSQL As String
strSQL = "SELECT * FROM t_Products WHERE Products='" & txtProductToFind.Text & "';"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open strSQL
Set .ActiveConnection = Nothing
End With
End Sub
[/tt]

Now if the User enters the following text into the txtProductToFind text box.
[tt]'; Drop Table t_Products;[tt]
Then the search text that you are going to be running against your Database will look like this.
[tt]"SELECT * FROM t_Products WHERE Products=''; Drop Table t_Products;';"[/tt]

Kinda scary. You could of course do checks for things like this. BUt if there is a lot of DB access that is a lot of checks to do.

Craig

"I feel sorry for people who don't drink. When they wake up in the morning, that's as good as they're going to feel all day."
~Frank Sinatra

 

[phzero]

Hi All,

I could not see the text so nicely on the blue background of the site. I thought it was a double-quote. However MarkSweetland, I need to thank you all for shedding light in my hour of darkness. IT WORKED!!! IT WORKED!!! IT WORKED!!! Have a great day. I know I will. Here's a star for each of you. Thanks again, it means a lot to me. CCLINT, once again you came through for me, thanks man. And thanks to you MarkSweetland for shedding light on the solution. Strongm, thank you for just taking the time to read and respond, a star for you as well.

 

[strongm]

No, it should not be a dilemma. We're not choosing to replace the single apostrophe with two apostrophes just for aesthetic reasons, you know. SQL Server treats it as a genuine single apostrophe.

 

[phzero]

In any event, the user is selecting this straight from a readonly listbox. If the string has to be input by the user, then you could always check and validate the value of the texbox at runtime using the Change event. I really don't see the harm in doing this. But thanks anyway for the warning Casper as it could have been overlooked by an inexperienced programmer. Your input here is appreciated.

 

[CCLINT]

I see no problems using single quotes.

You need use it anyways under certain circumstances (O'Brian).

If you use a command object and parameter object and then you do not have to worry about replacing anything in a Select statement's criteria, because the criteria is used as a command parameter, or worry about possible childish user attempts to destroy a database (This situation above is probably refering to JET).

In the Find statement, these attempts are not possible anyways.

 

[phzero]

CCLINT, I see your point. I guess the best thing would be to monitor the specified textbox for consecutive single quotes and programmatically remove the second one. I cannot think of a word in English where this situation occurs. Can you? Thanks anyway for the information.