No rows returned using SQLiteParameters, but rows returned with direct query

[Uranium 235]

Ok, here is the code in my class

   static public DataTable query\_search(string sqlite\_query, SQLiteParameter\[\] parameters)
    {
        SQLiteConnection sqlite\_conn = new SQLiteConnection("Data Source=test.db;Version=3;Compress=True;Synchronous=Full;");

        sqlite\_conn.Open();
        SQLiteCommand sqlite\_cmd;
        sqlite\_cmd = sqlite\_conn.CreateCommand();
        sqlite\_cmd.CommandText = sqlite\_query;
        SQLiteDataReader sqlite\_datareader;

        sqlite\_cmd.Parameters.AddRange(parameters);
        sqlite\_datareader = sqlite\_cmd.ExecuteReader();

        DataTable returnTable = new DataTable();
        returnTable.Load(sqlite\_datareader);

        writedebug(returnTable.Rows.Count.ToString());

        sqlite\_conn.Close();
        sqlite\_datareader.Close();
        return returnTable;
    }

writedebug writes 0 rows My call

    private void button1\_Click(object sender, EventArgs e)
    {
        if (search.Text.Trim() != "")
        {

            SQLiteParameter\[\] param = {
                new SQLiteParameter("%@search%", "paul") //used direct string for debug //search.Text)
            };

            customerList.Rows.Clear();

            DataTable searchinfo = database.query\_search("SELECT id, firstname, lastname FROM customers WHERE firstname LIKE '@search' OR lastname LIKE '@search'", param);

            MessageBox.Show(searchinfo.Rows.Count.ToString());
           
            foreach (DataRow row in searchinfo.Rows)
            {
                customerList.Rows.Add(row\[0\].ToString(), row\[1\].ToString() + " " + row\[2\].ToString());
            }
        }
    }

if I replace @search in the query with "%paul%" (in the database) writedebug turns 2 and it adds the rows to the form table I can query and put the search.Text directly into the string like SELECT id, firstname, lastname FROM customers WHERE firstname LIKE '" + search.Text + "' OR lastname LIKE '" + search.Text + '" but I needed sanitized queries (even though im the only one using this software), adding a % will make it return all rows I've used SQLiteParameter for INSERT INTO with another function successfully. please help thanks

[Lost User]

You cannot use "%@search% as the parameter name, as it is not a valid name. Try something like:

string psearch = "%" + search + "%";
SQLiteParameter[] param = {
new SQLiteParameter("search", psearch)
};

Then use "@search" in the SELECT clause as the parameter reference. NB the actual syntax of the command may not be exact.

[Uranium 235]

tried and failed. wait, using it without ' in the query string works. yes, thank you though I can still input % as a wildcard and get all rows. I thought this supposed to sanitize/escape input. guess I can live with it though, unless you have a better idea

[Lost User]

Yes, because parameter names must be entered 'as is', not quoted.

[Uranium 235]

do you know why the % char isnt being escaped?

[Lost User]

Not sure what you mean, I thought is was the wildcard character for the LIKE phrase.

[Uranium 235]

yes, but if it's put into the textbox, it's queried as LIKE %%% which matches everything

[Lost User]

I'm sorry, you have lost me. What has a TextBox got to do with SQL statements?

[Uranium 235]

it's a search query for a database, text input box

[Lost User]

Yes, but that has nothing to do with creating a valid SQL statement. The textbox is provided by the user, so your code should verify that it contains valid data. You then take the validated text and store into one of the SQL Parameters which get passed in to he execution module. Do not assume that the user knows what he or she is doing and just accept whatever they type. Many times it will be wrong, mist-typed, not understanding what is required, etc.

[Uranium 235]

I thought SQLLiteParameter sanitizes or at least escapes special characters

[Lost User]

Sorry, I don't know, you would need to check the documentation.

[Richard Deeming]

Parameters don't "sanitize" or "escape" special characters. They pass parameters across completely separately from the command text, so that there is no way for the database engine to get confused and treat part of the parameter as part of the command. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer