Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot use binary parameter values with "NO_BACKSLASH_ESCAPES" SQL mode #999

Closed
kpreisser opened this issue Jun 10, 2021 · 2 comments · Fixed by #1000
Closed

Cannot use binary parameter values with "NO_BACKSLASH_ESCAPES" SQL mode #999

kpreisser opened this issue Jun 10, 2021 · 2 comments · Fixed by #1000

Comments

@kpreisser
Copy link
Contributor

kpreisser commented Jun 10, 2021

Hi, we are using MySqlConnector 1.3.9 with a MySQL 8.0.25 DB. After opening the connection, we set the NO_BACKSLASH_ESCAPES SQL mode, but it seems that even when we set the MySqlConnectionStringBuilder.NoBackslashEscapes property, binary parameter values don't work correctly.

Consider the following program (running as .NET 5.0 console application):

static void Main(string[] args)
{
    var builder = new MySqlConnectionStringBuilder() {
        Server = "localhost",
        Port = 3306,
        UserID = "root",
        Password = "xxxx",
        AllowPublicKeyRetrieval = true,
        NoBackslashEscapes = true,
        Pooling = false,
        Database = "MyTestDb1"
    };

    using var con = new MySqlConnection(builder.ToString());
    con.Open();

    using var cmd = con.CreateCommand();

    cmd.CommandText = $"SET SESSION sql_mode = CONCAT((SELECT @@SESSION.sql_mode), ',NO_BACKSLASH_ESCAPES');";
    cmd.ExecuteNonQuery();    

    cmd.CommandText = $"CREATE TABLE IF NOT EXISTS MyTable1 (ID BIGINT, Bytes VARBINARY(512));";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = $"INSERT INTO `MyTable1` VALUES (1, @P1);";
    var binaryValue = Enumerable.Range(0, 256).Select(e => (byte)e).ToArray();
    cmd.Parameters.AddWithValue("@P1", binaryValue);
    cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
}

When running, it throws an exception:

MySqlConnector.MySqlException (0x80004005): 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 '()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuv' at line 2
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 50
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 135
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 444
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 266
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 101
   at mysql_repro.Program.Main(String[] args) in C:\Users\developer4\Desktop\mysql-repro\Program.cs:line 39

Edit: It seems the escaping logic for binary values has been overlooked when implementing #701. With commit kpreisser@70b8a4b, binary parameter values work correctly when setting NoBackslashEscapes to true.
Should I submit a PR?

Thank you!

kpreisser added a commit to kpreisser/MySqlConnector that referenced this issue Jun 10, 2021
@bgrainger
Copy link
Member

Should I submit a PR?

Thanks for the fix! Yes, please do.

kpreisser added a commit to kpreisser/MySqlConnector that referenced this issue Jun 11, 2021
See mysql-net#999

Signed-off-by: kpreisser <kpreisser@users.noreply.github.com>
@bgrainger
Copy link
Member

Fixed in 1.3.10.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

Successfully merging a pull request may close this issue.

2 participants