SQL Server query only works if no variables are empty

Question

I'm trying to create a query that will filter a table based on different HTML dropdown selections. I have 5 separate dropdowns and whenever I make a selection, the value is passed into a query (and stored until cleared) which populates the data on the page. However, this query only displays data if all selections have been made. I'm not seeing any errors either. Can someone explain to me why this is happening and offer a solution to where it displays data no matter the amount of dropdown selections that have been made?

$q = ($_GET['q']);
$date = ($_GET['data']);
$val = ($_GET['val']);
$rep = ($_GET['rep']);
$group = ($_GET['group']);

$sql ="SELECT [Customer]
      ,[Collector Name]
      ,[Bill Date]
      ,[Days Until Next Action]
      ,[Next Action]
      ,[Next Bill Date]
      ,[Billed Status]
      ,[Billed Amount]
      ,[Total Current Balance]
      ,CAST([Payment Due Date] as DATE) as [Payment Due Date]
      ,[Notes]
      ,[Payment Status]
      ,[Line Status]
        FROM [Test_Spec_Bill]
        WHERE ([Collector Name] = '$q' OR datalength([Collector Name]) = 0)
        AND ([Date Group - Filter] = '$date' OR datalength([Date Group - Filter]) = 0)
        AND ([Billed Status] = '$val' OR datalength([Billed Status]) = 0)
        AND ([Parent Report Code] = '$rep' OR datalength([Parent Report Code]) = 0)
        AND ([Balance Group - Filter] = '$group' OR datalength([Balance Group - Filter]) = 0)

Why are you checking datalength of everything? And why are you checking the column instead of the parameter. — Sean Lange
– Sean Lange, Commented Sep 10, 2018 at 15:08
I think you want to change your where conditions to use a case statement, so for example: WHERE [Collector Name] = CASE WHEN datalength($q) = 0 THEN [Collector Name] ELSE '$q' END — Ryan Wilson
– Ryan Wilson, Commented Sep 10, 2018 at 15:10
Just a code review note on my part (and I will admit that I don't know a lot about PHP), but this code seems very injectable to me. Does the rest of the PHP code have something to prevent SQLi? — Shawn
– Shawn, Commented Sep 10, 2018 at 15:34

Juan Carlos Oropeza · Accepted Answer · 2018-09-10 15:11:08Z

4

Check for the length of the variable not the field

Instead of

WHERE ([Collector Name] = '$q' OR datalength([Collector Name]) = 0)

Should be:

WHERE ([Collector Name] = '$q' OR datalength('$q') = 0)
                                             ^^^^

answered Sep 10, 2018 at 15:11

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Markov · Accepted Answer · 2018-09-10 15:13:19Z

Im not familier with php but for sql I think you first need to check if the variable is not null then use it, Maybe in ur php code you can default the value to null if the value is not selected.

SELECT
    [Customer]
   ,[Collector Name]
   ,[Bill Date]
   ,[Days Until Next Action]
   ,[Next Action]
   ,[Next Bill Date]
   ,[Billed Status]
   ,[Billed Amount]
   ,[Total Current Balance]
   ,CAST([Payment Due Date] AS DATE) AS [Payment Due Date]
   ,[Notes]
   ,[Payment Status]
   ,[Line Status]
FROM [Test_Spec_Bill]
WHERE (
('$q' IS NOT NULL
AND [Collector Name] = '$q')
OR DATALENGTH([Collector Name]) = 0
)
AND (('$date' IS NOT NULL
AND [Date Group - Filter] = '$date')
OR DATALENGTH([Date Group - Filter]) = 0
)
AND (('$val' IS NOT NULL
AND [Billed Status] = '$val')
OR DATALENGTH([Billed Status]) = 0
)
AND (('$rep' IS NOT NULL
AND [Parent Report Code] = '$rep')
OR DATALENGTH([Parent Report Code]) = 0
)
AND (('$group' IS NOT NULL
AND [Balance Group - Filter] = '$group')
OR DATALENGTH([Balance Group - Filter]) = 0
)

Joe McCarty · Accepted Answer · 2018-09-10 15:29:07Z

0

DATALENGTH = 0 will never evaluate a NULL value as zero because for a NULL value DATALENGTH returns NULL. You could deal with NULL values before sending the query or simply evaluate the NULL cases to true using IFNULL like this.

 WHERE ([Collector Name] = '$q' OR datalength(IFNULL([Collector Name], 0)) = 4)
        AND ([Date Group - Filter] = '$date' OR datalength(IFNULL([Date Group - Filter], 0)) = 4)
        AND ([Billed Status] = '$val' OR datalength(IFNULL([Billed Status], 0)) = 4)
        AND ([Parent Report Code] = '$rep' OR datalength(IFNULL([Parent Report Code],0)) = 4)
        AND ([Balance Group - Filter] = '$group' OR datalength(IFNULL([Balance Group - Filter],0)) = 4)

answered Sep 10, 2018 at 15:29

Joe McCarty

1451 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Server query only works if no variables are empty

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related