Trying to solve embedded SQL code syntax error

Question

I have a fairly simple web app that is used to let people interact with a local database. The embedded SQL is throwing the following error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near '<'.'

This only happens when the text box is empty. If it contains data everything works as it should. I would like for the query to essentially ignore the field if it is blank.

protected void findBTN_Click(object sender, EventArgs e)
{
    using (SqlConnection sqlCon = new SqlConnection(connString))
    {
        sqlCon.Open();
        string query = "SELECT * FROM[OEM_Ref] WHERE DrawID like CASE WHEN " + drawerIDbox.Text + " <> '' THEN '" + drawerIDbox.Text + "%' ELSE '%' End";

        SqlDataAdapter sqlDa = new SqlDataAdapter(query , sqlCon);
        DataTable dtbl = new DataTable();
        sqlDa.Fill(dtbl);

        GridView1.DataSource = dtbl;
        GridView1.DataBind();
    }
}

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Aug 07 '19 at 19:14
Possible duplicate of [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement) — Igor, Aug 07 '19 at 19:32

score 3 · Accepted Answer · answered Aug 07 '19 at 19:18

3

You don't have the single quotes around the text in your WHEN statement.

WHEN '" + drawerIDbox.Text + "' <> '' THEN '"

On a side note, you should really not concatenate user input this way; research SQL Injection.

answered Aug 07 '19 at 19:18

Wes H

4,186
2
13
24

Nailed it! I do know about SQL Injection attacks but this will only be on our local intranet, and so I'm not as concerned about it as I would be if it was on the public internet. I may still write a procedure for it though, Thanks for the help! – Garrett Pe Aug 07 '19 at 20:58

Trying to solve embedded SQL code syntax error

1 Answers1