How to fix Incorrect syntax near '0'

Question

string qry = @"select * 
               from tblProduct p 
               inner join tblColor c on c.colorID = p.ColorID
               inner join tblBrand b on b.brandID = p.brandID 
               where pName like'%" + txtSearch.Text + "%' and pPrice " + "between " + Convert.ToDouble(lblMin.Text) + " and " +" " + Convert.ToDouble(lblMax.Text) + "";

DataTable dt = mainClass.GetData(qry);

public static DataTable GetData(string qry)
{
    SqlCommand cmd = new SqlCommand(qry, con);

    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
            
    da.Fill(dt);

    return dt;
}

note// lblmin =0 and lblmax=7777

I will try in SQL Server not a problem but when I write query in C#, I get an error

Don't use string concatenation to create queries in the first place. This enables SQL injection. Imagine what would happen if the text field contained `'); DROP TABLE Users; --`. Or `' OR 1=1;--` — Panagiotis Kanavos, Aug 31 '23 at 06:56
Use parameterized queries instead of this. It's actually *easier* than concatenating strings. There are a lot of identical SO questions already. All *good* tutorial show how to use parameters. — Panagiotis Kanavos, Aug 31 '23 at 06:57
You should use the function APPLY_FILTER to safely apply the where clause. https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-sqlscript-reference/apply-filter — Remi sap, Aug 31 '23 at 07:01
Put a breakpoint and inspect your `qry` value [in debugger](https://ericlippert.com/2014/03/05/how-to-debug-small-programs/) and you will find that it doesn't look like you expect. Then change it to use parameters. — Filburt, Aug 31 '23 at 07:01
Try to check the content of your query, there may be spaces missing, e.g after "like" . — Arnaud, Aug 31 '23 at 07:02
`Convert.ToDouble(lblMin.Text)` and the implicit `.ToString()` that is applied on that to add it to the query string probalby produces a string like `123,34` ie a `,` instead of a `.` Set a breakpoint in your code, copy that exact value of `qry` your code generated and then try executing that in sql server ... — derpirscher, Aug 31 '23 at 07:20
Furthermore `and pPrice" + "BETWEEN "` will produce a string like `pPriceBETWEEN` which isn't valid either ... — derpirscher, Aug 31 '23 at 07:23

score -1 · Answer 1 · edited Aug 31 '23 at 08:09

I think the error is likely due to the way you are constructing your SQL query string. When you concatenate values into your query string, you need to ensure that the resulting SQL statement is valid, and also remember that directly putting user input into your SQL queries can be very dangerous.

It might let attackers manipulate your database. To avoid this, try using parameterized queries or a stored procedure instead.

Try like this:

string qry = @"select * from tblProduct p
               inner join tblColor c on c.colorID = p.ColorID
               inner join tblBrand b on b.brandID = p.brandID
               where pName like @SearchText
               and pPrice between @MinPrice and @MaxPrice";

using (SqlConnection con = new SqlConnection("YourConnectionStringHere"))
{
    con.Open();
    
    SqlCommand cmd = new SqlCommand(qry, con);
    cmd.Parameters.AddWithValue("@SearchText", "%" + txtSearch.Text + "%");
    cmd.Parameters.AddWithValue("@MinPrice", Convert.ToDouble(lblMin.Text));
    cmd.Parameters.AddWithValue("@MaxPrice", Convert.ToDouble(lblMax.Text));
    
    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    da.Fill(dt);

    return dt;
}

This solution has two problems: 1) SQL injection (txtSearch.Text may contain anything!), and 2) depending on the current culture, the double values might end up in the SQL string with a decimal separator other than '.'. — Klaus Gütter, Aug 31 '23 at 07:18
Much better now after the edit. One small remark still: you might need to esacpe some characters in `txtSearch.Text`, see https://stackoverflow.com/questions/25703637/escaping-special-characters-in-a-sql-like-statement-using-sql-parameters — Klaus Gütter, Aug 31 '23 at 08:05

How to fix Incorrect syntax near '0'

1 Answers1