Description:
When inserting or updating a string value with the character ’ into a database table using ByteFX.Data.MySqlClient in ASP.NET, An SQL syntax error is generated stating :
"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 '''' (...)"
How to repeat:
In ASP.NET (VB), create new page and paste the following code in the code-behind the page. Create a database table and add a column datatyp varchar or text or similar string types.
-------------------------------------------------------------------
Imports ByteFX.Data
Public Class TestBug
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim dbConn As MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection(Config.DatabaseConnectionString)
Dim dbComm As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand("UPDATE your_table_name SET your_column_text=@Text", dbConn)
dbComm.CommandType = CommandType.Text
Dim ParameterText As MySqlClient.MySqlParameter = New MySqlClient.MySqlParameter("@Text", MySqlClient.MySqlDbType.VarChar, 250)
ParameterText.Value = "This is chris’s test"
dbComm.Parameters.Add(ParameterText)
dbConn.Open()
dbComm.ExecuteNonQuery()
dbConn.Close()
End Sub
End Class
Suggested fix:
Parameters Value should be checked and replaced for such reserved characters like ’. In the meantime, I have to check my script before doing such database queries by the following:
Dim StringValue as String = "This is chris’s test"
ParameterText.Value = StringValue.Replace("’", "’’")
Description: When inserting or updating a string value with the character ’ into a database table using ByteFX.Data.MySqlClient in ASP.NET, An SQL syntax error is generated stating : "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 '''' (...)" How to repeat: In ASP.NET (VB), create new page and paste the following code in the code-behind the page. Create a database table and add a column datatyp varchar or text or similar string types. ------------------------------------------------------------------- Imports ByteFX.Data Public Class TestBug Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim dbConn As MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection(Config.DatabaseConnectionString) Dim dbComm As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand("UPDATE your_table_name SET your_column_text=@Text", dbConn) dbComm.CommandType = CommandType.Text Dim ParameterText As MySqlClient.MySqlParameter = New MySqlClient.MySqlParameter("@Text", MySqlClient.MySqlDbType.VarChar, 250) ParameterText.Value = "This is chris’s test" dbComm.Parameters.Add(ParameterText) dbConn.Open() dbComm.ExecuteNonQuery() dbConn.Close() End Sub End Class Suggested fix: Parameters Value should be checked and replaced for such reserved characters like ’. In the meantime, I have to check my script before doing such database queries by the following: Dim StringValue as String = "This is chris’s test" ParameterText.Value = StringValue.Replace("’", "’’")