Re: mysql_real_escape_string and VB.Net/ASP.Net
I tried removing my escaping code completely and it errors. Here is the error page text:
Server Error in '/PressRelease' Application.
--------------------------------------------------------------------------------
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'S HEARING SCREENED AT MASON GENERAL HOSPITAL',' Effective Janu
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'S HEARING SCREENED AT MASON GENERAL HOSPITAL',' Effective Janu
Source Error:
Line 112: End With
Line 113: cn.Open()
Line 114: cmd.ExecuteNonQuery()
Line 115: cn.Close()
Line 116: Response.Redirect("http:../Default.aspx")
Source File: c:\inetpub\wwwroot\PressRelease\NewPR.aspx.vb Line: 114
Stack Trace:
[MySqlException: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'S HEARING SCREENED AT MASON GENERAL HOSPITAL',' Effective Janu]
MySql.Data.MySqlClient.PacketReader.CheckForError()
MySql.Data.MySqlClient.PacketReader.ReadHeader()
MySql.Data.MySqlClient.PacketReader.OpenPacket()
MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst)
MySql.Data.MySqlClient.CommandResult..ctor(Driver d, Boolean isBinary)
MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader)
MySql.Data.MySqlClient.MySqlCommand.Consume()
MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
PressRelease.NewPR.butAdd_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\PressRelease\NewPR.aspx.vb:114
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
As you can see, the error is ocurring because there is an unescaped single quote (apostrophe) in the string.
Here is my code, if it will help.
Dim pIdx As New MySqlParameter("?Idx", MySqlDbType.Int32)
Dim pTitle As New MySqlParameter("?Title", MySqlDbType.String)
Dim pBody As New MySqlParameter("?Body", MySqlDbType.String)
Dim pReleaseDate As New MySqlParameter("?ReleaseDate", MySqlDbType.Date)
Dim pExpireDate As New MySqlParameter("?ExpireDate", MySqlDbType.Date)
Dim pHasImage As New MySqlParameter("?HasImage", MySqlDbType.Byte)
Dim pDocument As New MySqlParameter("?Document", MySqlDbType.Blob)
Dim pImage As New MySqlParameter("?Image", MySqlDbType.Blob)
pIdx.Value = ControlChars.NullChar
pTitle.Value = txtTitle.Text
pBody.Value = txtBody.Text
pReleaseDate.Value = CDate(txtReleaseDate.Text)
pExpireDate.Value = CDate(txtExpireDate.Text)
If Not inpDocument.Value = "" Then
Dim MyFileCollection As HttpFileCollection
Dim MyFile As HttpPostedFile
Dim FileLen As Integer
Dim MyString As String
Dim fs As System.IO.Stream
MyFileCollection = Request.Files
Dim x As Integer = MyFileCollection.Count
MyFile = MyFileCollection(0)
FileLen = MyFile.ContentLength
Dim Input(FileLen) As Byte
fs = MyFile.InputStream
fs.Read(Input, 0, FileLen)
pDocument.Value = Input
Else
pDocument.Value = ControlChars.NullChar
End If
If Not inpImage.Value = "" Then
Dim MyFileCollection As HttpFileCollection
Dim MyFile As HttpPostedFile
Dim FileLen As Integer
Dim MyString As String
Dim fs As System.IO.Stream
MyFileCollection = Request.Files
Dim x As Integer = MyFileCollection.Count
If x = 2 Then
MyFile = MyFileCollection(1)
ElseIf x = 1 Then
MyFile = MyFileCollection(0)
End If
FileLen = MyFile.ContentLength
Dim Input(FileLen) As Byte
fs = MyFile.InputStream
fs.Read(Input, 0, FileLen)
pImage.Value = Input
pHasImage.Value = 1
Else
pImage.Value = ControlChars.NullChar
pHasImage.Value = 0
End If
With cmd.Parameters
.Add(pIdx)
.Add(pTitle)
.Add(pBody)
.Add(pReleaseDate)
.Add(pExpireDate)
.Add(pHasImage)
.Add(pDocument)
.Add(pImage)
End With
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()