Take the 2-minute tour ×

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

share|improve this question
Use parameters to prevent SQL injection here. –  Tim S. Dec 12 '12 at 17:33
Hello Tim... I am using a parameter –  EagleFox Dec 12 '12 at 17:41
@EagleFox I added an important point to my answer. I hope this will help ;-) –  jbl Dec 12 '12 at 20:23
Thank you jbl... much appreciated sir –  EagleFox Dec 12 '12 at 21:15
add comment

4 Answers

up vote 1 down vote accepted

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 '%'

share|improve this answer
Thanks jbl... :)... I am really new to sql... so thanks for the heads up... really don't know what sanitizing means but will look up here... thanks again –  EagleFox Dec 12 '12 at 17:20
add comment

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");
share|improve this answer
Thanks Marc... I knew it was something like that... but I am using c# –  EagleFox Dec 12 '12 at 17:21
add comment
SELECT firstname, lastname
FROM myTable
WHERE lastname LIKE '" + parameter + "%'
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.

share|improve this answer
thanks treborbob... m trying ur response –  EagleFox Dec 12 '12 at 17:21
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 stackoverflow.com/questions/5468425/…), 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
add comment

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.

share|improve this answer
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
add comment

Your Answer


