sql and apostrophe's!

[toon10]

Hi

I have a routine, which reads in one dataset and then writes back to an sql table. This worked fine but someone has added an account code, which has an apostrophe in it!

My sql2 statement falls over when it hits this record. Anyone know how to get around this? I presume I need to be able to add another apostrophe to my “MyDataSet.Tables(0).Rows(dsCounter).Item(0)” statement but I’m not sure how to do that!

Here’s my code:

…code to fill MyDataSet

Dim dsCounter As Integer
Dim Sql2 As String

MyDataSet2 = New DataSet
With MyDataSet.Tables(0)
For dsCounter = 0 To .Rows.Count - 1 'loop
If MyDataSet.Tables(0).Rows(dsCounter).Item(1) Is System.DBNull.Value Then
Sql2 = "UPDATE tblDRAMisc SET tblDRAMisc.Colour = 'Green' WHERE tblDRAMisc.[Account Code] = '" & MyDataSet.Tables(0).Rows(dsCounter).Item(0) & "'"
MyDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter(Sql2, MyConnection)
MyDataAdapter2.Fill(MyDataSet2, "tblDRAMisc")
End If
Next
End With

Thanks
Andrew

 

[ThatRickGuy]

you can use string.replace to remove the ' or to add an escape char.

MyDataSet.Tables(0).Rows(dsCounter).Item(0).ToString.Replace("'", "\'")

-Rick

----------------------

http://www.ringdev.com

 

[toon10]

Thanks for the prompt and helpful information. That worked a treat!

Cheers again
Andrew

 

[chrissie1]

and that was also one of the reason's why you should use parameters. And perhaps to avoid sql-injection

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ThatRickGuy]

Agreed! Specifically in a web environment. If you are using any of the posted variable in your SQL statement, you are opening yourself to attack.

such as:
index.asp?id=1

using % tags in the address bar can easily make it:

index.asp?id=1' or ''='

if you us a SQL String like this:

strSQL = "SELECT * FROM Data WHERE ID = '" & id & "'"

the out sql string would wind up being:

SELECT * FROM Data WHERE ID = '1' OR ''=''

the ''='' will always be true, which means that the person doing the injection can get all of the records out of your table. This is just a minor example of what can be done. Using stored procedures can prevent this type to exploit from working. If you can't use stored procedures, make sure you atleast make sure the value you are using is clean.

-Rick

----------------------

http://www.ringdev.com

 

[dolfo35]

Hi Rick, and everyone else. Would this work the same with the ampersand sign (&). I have a field in SQL Server that has an & in some of the values. I tried using the solution mentioned in this thread, but I might have done something wrong. If this works with & do I change the syntax at all?

Rudy

 

[ThatRickGuy]

You would need to change

Replace("'", "\'")

to

Replace("&", "\&")

I think that should work. Not sure though, & might not be so easily escaped.

Which brings up a good point about relational database design. Your actual keys should be non-logic containing numeric only. You can make another key-like field that has some value the user likes, but don't use that for linking.

For example, one employer I had used the InvoiceNumber as a primary key. Except the invoice number contained the year/month of the invoice. In some situations (I can't remember exactly anymore) it caused huge problems. Also, using a User entered value as a key is dangerous as you can wind up with control characters (\ ' " & % etc) in the key, which just makes life a pain.

-Rick

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[chiph]

If your database offers auto-generated keys (Indentity columns in SQL Server), you should always use them as keys instead of relying on some attribute of the data (like an invoice number). In the case of invoice numbers, one customer I had relied on his printed forms, which had an increasing number printed on them. And then he changed to another printer, who started the numbering over at 000001. Ooops.

Also, the ampersand problem would also be solved by using ADO.NET parameter objects. If you're doing string concatenation to build your SQL commands, STOP! You're leaving yourself wide open to a SQL injection attack, as well as odd conditions like storing invoices from "O'Brian & Sons Plumbing".

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ThatRickGuy]

And then he changed to another printer, who started the numbering over at 000001. Ooops.

hehehe, did they demand to have the old printer re-installed?

Also, the ampersand problem would also be solved by using ADO.NET parameter objects

I agree! It takes a little getting used to but this is definately the prefered tool.

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[hitechboy78737]

I solved this problem by making 2 functions that I use anytime I'm reading or writing a string field to a database.

I envoke it like:

(to the database)
ksqltextall(myvarString)

 'text to SQL
    Public Function kSQLTextAll(ByVal strToCheck As String) As String
        Try
            If IsDBNull(strToCheck) Then
                Return ""
            ElseIf strToCheck = "" Then
                Return ""
            Else
                strToCheck = Replace(strToCheck, Chr(34), "~``~")
                strToCheck = Replace(strToCheck, "'", "~`~")
                strToCheck = Replace(strToCheck, Chr(59), "~;~")
                strToCheck = Replace(strToCheck, ";", "~;~")
                strToCheck = Replace(strToCheck, ".", "~.~")
                strToCheck = Replace(strToCheck, Chr(38), "~&~")
                strToCheck = Replace(strToCheck, "&", "~&~")
                strToCheck = Replace(strToCheck, "<", "~<~")
                strToCheck = Replace(strToCheck, ">", "~>~")
                strToCheck = Replace(strToCheck, Chr(35), "~#~")
                strToCheck = Replace(strToCheck, "#", "~#~")
                strToCheck = Replace(strToCheck, "{", "~{~")
                strToCheck = Replace(strToCheck, "}", "~}~")
                strToCheck = Replace(strToCheck, "(", "~(~")
                strToCheck = Replace(strToCheck, ")", "~)~")
                strToCheck = Replace(strToCheck, "&", "~&~")
                strToCheck = Replace(strToCheck, "+", "~+~")
                strToCheck = Replace(strToCheck, "/", "~/~")
                strToCheck = Replace(strToCheck, "%", "~%~")
                Return strToCheck
            End If
        Catch ex As Exception
            showError(ex)
        End Try
    End Function

(from the database)
rsqltextall(myvarString)

    Public Function rSQLTextAll(ByVal strToCheck As String) As String
        Try
            If IsDBNull(strToCheck) Then
                Return ""
            ElseIf strToCheck = "" Then
                Return ""
            Else
                strToCheck = Replace(strToCheck, "~``~", Chr(34))
                strToCheck = Replace(strToCheck, "~`~", "'")
                strToCheck = Replace(strToCheck, "~;~", Chr(59))
                strToCheck = Replace(strToCheck, "~;~", ";")
                strToCheck = Replace(strToCheck, "~.~", ".")
                strToCheck = Replace(strToCheck, "~&~", Chr(38))
                strToCheck = Replace(strToCheck, "~&~", "&")
                strToCheck = Replace(strToCheck, "~<~", "<")
                strToCheck = Replace(strToCheck, "~>~", ">")
                strToCheck = Replace(strToCheck, "~#~", Chr(35))
                strToCheck = Replace(strToCheck, "~#~", "#")
                strToCheck = Replace(strToCheck, "~{~", "{")
                strToCheck = Replace(strToCheck, "~}~", "}")
                strToCheck = Replace(strToCheck, "~(~", "(")
                strToCheck = Replace(strToCheck, "~&~", "&")
                strToCheck = Replace(strToCheck, "~+~", "+")
                strToCheck = Replace(strToCheck, "~/~", "/")
                strToCheck = Replace(strToCheck, "~%~", "%")
                Return strToCheck
            End If
        Catch ex As Exception
            showError(ex)
        End Try
    End Function

These 2 functions have served me well on both web and desktop projects... hope it is useful to you as well!

Kevin Howell
Briefcase of Talent
Austin, Texas

 

[chiph]

One other benefit to using ADO.NET parameter objects is the query is much more likely to end up in the database's procedure cache. This is desirable because it means that if the database recognizes the query on subsequent use, it will pull the precompiled copy from cache, and won't need to parse the SQL, check it for correctness, make sure all the tables & columns it uses exist, etc. prior to actually doing the work of executing it.

You'll get 80-90% of the speed benefit that using a stored procedure would give you, but with much less work.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first