Click here to Skip to main content
15,911,785 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello all. Am new to VB.Net and seeking some assistance. With some help from the internet, I managed to save a richtext document to SQL server and then access for viewing. 

How do I write an update statement to save it back to SQL? 

Thanks in advance.


What I have tried:

Dim cmd As New SqlCommand("Select richtext from tablename", con)

       Try
           rtbdoc.SaveFile("Test.rtf")
           Dim Stream = New FileStream("Test.rtf", FileMode.Append, FileAccess.ReadWrite)
           Dim size As Integer = Convert.ToInt32(Stream.Length)
               Dim rtf As [Byte]() = New [Byte](size - 1) {}
               Stream.Read(rtf, 0, size)

               Dim paramRTF As New SqlParameter("@rtf", SqlDbType.VarBinary, rtf.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, rtf)

          cmd.CommandText = "Update table SET firstname = '" & txtfirstname.Text & "', lastname = '" & txtlastname.Text & "' richtext = @rtf where id = @id"

           If con.State = ConnectionState.Open Then con.Close()
           con.Open()

           cmd.Parameters.Add("@id", SqlDbType.Int).Value = txtid.Text
           cmd.Parameters.Add(paramRTF)
           'cmd.ExecuteNonQuery(
           cmd.ExecuteNonQuery()

           MessageBox.Show("File Updated Successfully")
           clear()
       Catch ex As Exception
           MsgBox("Error updating record!", MsgBoxStyle.Critical)
           clear()
       Finally
           If Stream.Null IsNot Nothing Then
               Stream.Null.Close()
           End If
       End Try
Posted
Updated 25-Oct-18 15:29pm

1 solution

I don't understand, you build SQL queries subject to injection, but you know how to secure them with parameters:
VB
cmd.CommandText = "Update table SET firstname = '" & txtfirstname.Text & "', lastname = '" & txtlastname.Text & "' richtext = @rtf where id = @id"

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
 
Share this answer
 
Comments
Member 13689209 27-Oct-18 19:04pm    
Thank you for your response. Appreciate your feedback. However, like I said, I am new to this. This is my first solo application project out of university, and am learning as I go along.

I figured out how to solve it. Will save the links for future reference.

Thanks again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900