Why is this SQL query not retrieving any data?

Question

I'm trying to get a DataTable containing Houses in a specific city with rooms between x and y.

I'm using this SQL query:

"SELECT * FROM Houses WHERE City = '" + Cities.Text + 
"' AND Rooms BETWEEN '" + MinRooms.Text + "' AND '" + MaxRooms.Text + "'"

The controls are dropdownlists in ASP.NET.

When I remove it and leave only the "between rooms" it works.

What should I look for? Everything seems to be correct.

Remove ' (apostrophes) used with BETWEEN - just in case Rooms is a numeric column. — bjnr, Dec 09 '13 at 21:55
As an important aside, your code is vulnerable to **SQL injection attacks** (Google it! Very important!). To avoid this you need to implement something called **parameterized queries** (Google it as well). — System Down, Dec 09 '13 at 21:57
Put a debug breakpoint the line after you build the SQL string and take a look at the query. Does it look like you expect it to? Also, note that building queries as string from user input can lead to SQL Injection; please use [parameterized queries](http://bobby-tables.com/csharp.html) instead. — Ed Gibbs, Dec 09 '13 at 21:58
Your approach of using the .Text property directly is VULNERABLE to SQL injection. You should parameterize your query. See http://stackoverflow.com/questions/1131332/sql-injection-attack-on-asp-registration-form-pages. — codechurn, Dec 09 '13 at 21:58
thanks for the replies. i already tried removing the apostrophes, it didnt work. if this query supposed to work - i guess i have to look deeper into the code.. :( about the injection, thanks for the tip! — Adam Bensh, Dec 09 '13 at 22:02
Did you output the query text after you dangerously injected the form field values? Did you try running that query directly, outside of the application? — Aaron Bertrand, Dec 09 '13 at 22:05
@Shiva - It's an important bit of advice I wished someone had given me when I was starting up myself :) — System Down, Dec 09 '13 at 22:08

Tim Schmelter · Accepted Answer · 2013-12-09T22:12:50.090

I assume Rooms is an int column, therefore you need to remove the apostrophes which are for text columns. But you should use sql-parameters to prevent sql-injection anyway:

string sql = @"SELECT h.* FROM Houses h 
              WHERE City = @City 
              AND Rooms BETWEEN @MinRooms AND @MaxRooms";
var table = new DataTable();
using(var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
using(var da = new SqlDataAdapter(sql, con))
{
    da.SelectCommand.Parameters.AddWithValue("@City", Cities.Text);
    da.SelectCommand.Parameters.AddWithValue("@MinRooms",int.Parse(MinRooms.Text));
    da.SelectCommand.Parameters.AddWithValue("@MaxRooms",int.Parse(MaxRooms.Text));
    da.Fill(table);
}

score 1 · Answer 2 · answered Dec 09 '13 at 21:58

1

    String sql = String.Format("SELECT * FROM Houses WHERE City = '{0}' AND Rooms BETWEEN {1} AND {2}",
    Cities.Text, MinRooms.Text, MaxRooms.Text);

Don't ignore SQL injection issue...

answered Dec 09 '13 at 21:58

bjnr

3,353
1
18
32

2

Using `string.Format` is not a replacement for sql-parameters(just to clarify ) – Tim Schmelter Dec 09 '13 at 22:05

Why is this SQL query not retrieving any data?

2 Answers2