CODEWITHSUNDEEP
c#sqlwinforms
az4dan
az4dan

Reputation: 659

OleDbCommand not escaping properly

I'm using bound parameters, but somehow this code won't escape the [ character. What's going on?

string query = "SELECT * FROM Items LEFT JOIN [Categories] ON Items.[category]=[Categories].[ID] WHERE ";
query += " supplier LIKE @supplier";
using (OleDbCommand cmd = connection.CreateCommand())
{
    // create command with placeholders
    cmd.CommandText = query;


    // add named parameters
    cmd.Parameters.AddRange(new OleDbParameter[]
    {
        new OleDbParameter("@supplier", "%"+supplier+"%"),
    });

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataSet dset = new DataSet();
    da.Fill(dset);

    return dset;

Passing a string with the [ character results in an error with the message shown below

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Invalid pattern string

I have tried prepending [ with backslash but the error does not simply go away.

Upvotes: 3

Views: 664

Answers (1)

Steve
Steve

Reputation: 216273

According to this document, the [ and ] characters when used with the LIKE operator denote a charlist like the ones used in regular expression.

This means that a single square bracket is not correctly interpreted and gives the error.
From the same document you can read

You can use the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets.

So, if you need to search for a single square bracket you need to enclose it in opening and closing square brackets.

if(supplier.IndexOf('[') >= 0)
    supplier = supplier.Replace("[", "[[]");

Upvotes: 3

Related Questions