Update record using parameterized query in Asp.net VB

I have a record that I want to update in a SQL Server database.  I want to use a parameterized query to reduce the chances of SQL Injection attack, but I just can't get the syntax correct.

I want to update the fields "Address" and "Phone" who's values come from txtAddress.text and txtPhone.text

Please supply code in VB.
rrhandle8Asked:
Who is Participating?
 
sameer_goyalConnect With a Mentor Commented:
No, the datatype is not required for parameterized queries. However, when working with Stored procs, you will need to define the datatype.

Using parameterized queries help prevent sql injection to a large extent but i suggest you use stored procs instead of queries. That way, you can handle illegitimate characters, if any, in the params more gracefully and handle it within the database,

For instance, you can examine the value in the parameter in more detail inside a stored procedure but that possibility is rather rare when working with queries.

let me know if that helps.
0
 
sameer_goyalCommented:
Considering you have the DB connection/etc code in place, here's what you can do

Dim updateSql As String

updateSql = "Update dbo.<replace you table name here> set Address = '" & txtAddress.Text.Trim().Replace("'", "''") & "', Phone = '" & txtPhone.Text.Trim().Replace("'", "") & "' WHERE <use condition clause if any>"

hope it helps

Note:
txtAddress.Text.Trim().Replace("'", "''") : the first parameter in Replace is a single quote mark and the second one is 2 single quotes within the quotations. This is to prevent exception due to an appostrophe which can be part of the text. This will also prevent Sql Injection

In the replace for phone, I am just removing ' because it is not required in a phone number
0
 
rrhandle8Author Commented:
sameer_goyal

That is just a standard SQL update command.  I am trying to do it with a parameterized query like:

cmd.Parameters.AddWithValue("@Phone", txtPhone.text)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
sameer_goyalCommented:
Here you go

Dim updateSql As String
Dim cmd As SqlCommand
strQuery = "update <tableName> set Address=@Address, phone = @Phone where <you condition>"
cmd = New SqlCommand(updateSql)
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.Trim())
cmd.Parameters.AddWithValue("@Phone", txtPhone.Text.Trim())

Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString;
        Dim con As New SqlConnection(strConnString)
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Response.Write(ex.Message)
            Return False
        Finally
            con.Close()
            con.Dispose()
        End Try

This is it.
0
 
rrhandle8Author Commented:
That works with 1 correction: strQuery should be UpdateQuery
 
strQuery = "update <tableName> set Address=@Address, phone = @Phone where <you condition>"


One question: Shouldn't the datatype be stated some place like SqlDbType.NVarChar?  The whole reason I am trying to do this with parameterized queries is to avoid SQL injection attacks.  Does doing it the way you have it written help prevent attacks?
0
 
rrhandle8Author Commented:
Good answer, and thank you for the code!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.