Why is SQL statement returning all rows when passing " "?

Question

I am using this SQL statement to get rows:

SELECT firstname, lastname 
FROM myTable 
WHERE ((lastname LIKE '" + parameter + "%')

parameter gets a value form a textbox. The default value of textbox is " "

My problem is I am getting all the rows when nothing is entered in the textbox. I tried to use

WHERE lastname =

This simply gives me all the records that has " "

What is the correct way of getting data that excludes blanks in the database and also does not give your any record when blank or " " is passed as a parameter

@EagleFox I added an important point to my answer. I hope this will help ;-) — jbl, Dec 12 '12 at 20:23

score 2 · Answer 1 · answered Dec 12 '12 at 17:13

You're using a wilcard, so for a blank search string, you end up with

... WHERE lastname LIKE '%'

which will match everything. If nothing is entered into the search form, you should simply detect that at the script level and NOT run the query, e.g. in php-ish terms:

if (strlen($_GET['keywords']) == 0) {
   die("No search terms entered");
}

Treborbob · Answer 2 · 2012-12-12T17:22:52.607

1

SELECT firstname, lastname
FROM myTable
WHERE lastname LIKE '" + parameter + "%'
AND NULLIF(lastname,'') IS NOT NULL
AND '" + parameter + "' <> ''

Quite simply only return records where lastname startswith your 'parameter' and only where 'parameter' is not empty string.

Edit: Added code for the requirement that it never returns record with blank lastname.

edited Dec 12 '12 at 17:22

answered Dec 12 '12 at 17:13

Treborbob

1,221
1
8
18

1

As others have said though, you probably want to just not run anything if your param is blank, and whatever you use in a SQL query that isn't paramaterised (more here http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection), you must be sure is safe to prevent SQl Injection. – Treborbob Dec 12 '12 at 17:24
Thanks Treborbob... This is exactly what I did... on my controller i checked to see if the passed parameter was empty... if it's not then I executed the method... thanks so much – EagleFox Dec 12 '12 at 17:49

jbl · Accepted Answer · 2012-12-12T20:19:27.903

First, I hope you do some sanitizing to avoid sql injection.

Then you can at your choice :

not run the query if sanitizing parameter including a trim() is empty
have SELECT firstname, lastname FORM myTable WHERE ((lastname LIKE '" + parameter + "%') AND ''<>'" + parameter + "'"

the second one being a bad practice from a performance point, and it would assume you've given up sanitizing (very bad)

Also you should note that, in your sanitizing process, you will have to escape or remove special characters _ and %, see http://msdn.microsoft.com/en-us/library/ms179859(v=sql.105).aspx

I you do not handle this, a non empty parameter equal to % will lead to a like '%%', which is equivalent to like '%'

score 0 · Answer 4 · answered Dec 12 '12 at 17:11

0

Well you could just test for string.IsNullOrEmpty on parameter variable in your code so that you don't even need to make the database call.

In my view its the most efficient thing to do.

answered Dec 12 '12 at 17:11

The Unculled Badger

760
4
18

Thanks for your quick answer unculled... but I am using a store that fires the controller... and everytime I do an update I have reload the store, so this will not work if I am reloading the store. – EagleFox Dec 12 '12 at 17:14

Why is SQL statement returning all rows when passing " "?

4 Answers4