handling special characters when updating database

[davikokar]

Hallo,
When I submit a new entry to my datalist I get errors if the text contain some special characters. For example the quote ' would create a problem in the slq statement (using an Access db), and html tags (like <br /> would stop the application with a warning for dangerous content. Is there a way to convert this special characters to others before the errors are generated?

thanks

 

[ca8msm]

If you use parameter based inserts then special characters such as a ' (quote) will be taken care of automatically. HTML tags are not special characters but these can be taken care of using regular expressions.

The other alternative is to simply use the Replace function on the string you are inserting.



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

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[davikokar]

how does it work the parameter based insert? Do the work with Access?
Now I'm using a sql statement in the updatecommand, like:

...

Dim strText As Integer
strText = e.CommandArgument
 
Dim strSQL As String = "UPDATE tbl_messages SET tbl_messages.mes_text = '" & strTextMes & "', tbl_messages.mes_title = '" & strTextTitle & "' WHERE tbl_messages.mes_ID = " & strText & ";"
...

Do you have a link or a suggestion? thanks

 

[ca8msm]

Here's an example using the SQL Server Northwind database. You can easily adapt this to any database type.

        Try
            Dim MyConnection As New SQLConnection("server=LOCALHOST;User id=SA;password=;database=Northwind")
            Dim MyUpdateCommand As SqlCommand = New SqlCommand
            MyUpdateCommand.Connection = MyConnection
            Dim sql As String
            sql = "Update categories Set categoryName = @CatName WHERE CategoryID = @CatID"

            MyUpdateCommand.CommandText = sql

            MyUpdateCommand.Parameters.Add("@CatName", SqlDbType.NVarChar, 250).Value = "Category Name"
            MyUpdateCommand.Parameters.Add("@CatID", SqlDbType.Int).Value = 14
            MyConnection.Open()
            MyUpdateCommand.ExecuteNonQuery()
        Catch ex As Exception
            Response.Write(ex.ToString())
        Finally
            MyConnection.Close()
        End Try

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

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[davikokar]

Hi ca8msm,

i was trying your solution but I get stucked with the sqlcommand: "BC30002: Type 'SqlCommand' is not defined".

Do you know if this work with a ms access db, or if I have to import some namespace? thanks

 

[ca8msm]

As I said that is an example specifically for a SQL Server database. You will have to use the ODBC namespace for MS Access.

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

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[davikokar]

thanks ca8msm, I found the right commands for ms access:

here the solution

    Try
            

            Dim MyUpdateCommand As OleDbCommand = New OledbCommand
            
            MyUpdateCommand.Connection = MyConnection
            
            Dim sql As String
            
            sql = "Update tbl_messages SET tbl_messages.mes_text = @MesText, tbl_messages.mes_title = @MesTitle WHERE tbl_messages.mes_ID = @MesID;"


            MyUpdateCommand.CommandText = sql

            
            MyUpdateCommand.Parameters.Add("@MesText", OleDbType.VarChar, 250).Value = strTextMes

            MyUpdateCommand.Parameters.Add("@MesTitle", OleDbType.VarChar, 250).Value = strTextTitle
            
            MyUpdateCommand.Parameters.Add("@MesID", OleDbType.Integer).Value = e.CommandArgument
            
            MyConnection.Open()
            
            MyUpdateCommand.ExecuteNonQuery()
        
        
        Catch ex As Exception
        
        Response.Write(ex.ToString())
        
        Finally
        
        MyConnection.Close()
        
        End Try

 

[ca8msm]

Glad to help - remeber this will only help with things such as the ' quote and you will still have to use regular expressions if you want to strip the HTML tags.

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

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chiph]

karerda -
Using the OleDbParameter objects will help you in one other important way - it will protect you from SQL Injection attacks, where an attacker is able to put SQL commands into a textbox in your app, and have then executed by the database.

A good walk-thru of an attack is at:

http://www.unixwiz.net/techtips/sql-injection.html

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first