building a query string

[stevie503]

Guys, im having a wee problem when building a query string.

I have a search menu where the user selects options from a drop down menu and then this builds up the search query string. I have a Pass and Fail options from a drop down menu which work. I have changed it slightly now as a Pass can be any of the following: TS/Pass, NC/Pass or Mixed/Pass or it completely fails.

So now in my drop down have all of the above plus an overall Pass option, so it looks like: Pass, TS/Pass, NC/Pass, Mixed/Pass and Fail. I can now look explicitly for a NC/Pass now. But what my issue is that I want it so it I click on the Pass option it will search for TS/Pass, NC/Pass, Mixed/Pass results.

Can seem to get those 3 options strung together correctly.
Here is my code:

$QBuild = "";
$QBuild .= "SELECT * FROM ($table) WHERE($field)";

	if($Date1 && $Date2 != NULL){
		$QBuild .= "AND (Date BETWEEN '$Date1' AND '$Date2') ";
	}

	if($Initials != NULL){
		$QBuild .= "AND (Initials LIKE '$Initials')" ;
	}

	if($PassFail == 'Pass'){
		$QBuild .= "AND (PassFail LIKE 'TS/Pass' || 'NC/Pass' || 'Mixed/Pass')" ;
		}
		
		
		if($PassFail != NULL){
		$QBuild .= "AND (PassFail LIKE '$PassFail')" ;
		}
	
	if($circuit != NULL){
		$QBuild .= "AND (circuit LIKE '$circuit')" ;
	}

Thanks

 

[sw1ft]

There's problem in your SQL, you can't do:

"AND (PassFail LIKE 'TS/Pass' || 'NC/Pass' || 'Mixed/Pass')"

you would need to do:

"AND (PassFail LIKE 'TS/Pass' || PassFail LIKE 'NC/Pass' || PassFail LIKE 'Mixed/Pass')"

Which can, by using wildcard characters, be simplified to:

"AND (PassFail LIKE '%/Pass')"

Or if you want to search exact terms:

"AND (PassFail IN ('TS/Pass','NC/Pass','Mixed/Pass'))"

 

[SimonCHere]

what is the SQL language as I don't think

SELECT * FROM ($table) 
WHERE($field)
AND (PassFail LIKE 'TS/Pass' || 'NC/Pass' || 'Mixed/Pass')

will work

you could have

if($PassFail == 'Pass'){
    $QBuild .= "AND (PassFail = 'TS/Pass' OR PassFail = 'NC/Pass' OR PassFail = 'Mixed/Pass')" ;
}

But why not just have

if($PassFail == 'Pass'){
    $QBuild .= "AND (PassFail LIKE '%Pass')" ;
}

However, the next line of code is wrong as if you have "Pass" as your input, it will give you

SELECT * FROM ($table) 
WHERE($field)
AND (PassFail = 'TS/Pass' OR PassFail = 'NC/Pass' OR PassFail = 'Mixed/Pass')
AND (PassFail LIKE 'Pass')

your code needs to check if it's null, then check if it's "Pass". If it's not "Pass" then do the PassFail like '$Passfail' line.

 

[sw1ft]

As SimonCHere has pointed out, this block needs to be changed:

if($PassFail != NULL){
	$QBuild .= "AND (PassFail LIKE '$PassFail')";
}

As you need it to act more like an else, so:

if($PassFail == 'Pass'){
	$QBuild .= "AND (PassFail LIKE '%/Pass')";
} else if ($PassFail != NULL){
	$QBuild .= "AND (PassFail LIKE '$PassFail')";
}

You then have the error here:

"AND (PassFail LIKE '$PassFail')";

Which is going to search for a PassFail LIKE "$PassFail" rather than the value of the variable $PassFail.

I think you need to do something like this:

"AND (PassFail LIKE '" . $PassFail . "')";

Even so, conceptually this isn't great, you should validate the value of $PassFail, as at the moment if it's not 'Pass' or null you're going to accept it which has massive security risks.

I'm not 100% sure of the syntax for php as I haven't used it in over 5 years but I would have a function that uses a switch to return a value that you can enforce, something like:

function getPassFail($passFail){
    switch ($passFail) :
        case  "Pass": return "%/Pass";
        case  "TS/Pass": return "TS/Pass";
        case  "NC/Pass": return "NC"/Pass";
        case  "Mixed/Pass": return "Mixed/Pass";
        case  "Fail": return "Fail";
        default // handle the exception/any injection attempts here
    endswitch;
}

And then do something like:

$QBuild .= "AND (PassFail LIKE '" . getPassFail($PassFail) . "')";

I would suggest employing this technique on the other fields as well, putting a value into a SQL query just because it's not null is extremely high risk.

 

[SimonCHere]

Listen to sw1ft
My Php is none existent so didn't know if you can do the "like '$PassFail'" bit, but he is right about SQL injection.
Although slightly mitigated as the input is coming from a drop down list you have created, it can easily be spoofed.

 

[pprest123]

Common Lisp: one test case off by 5 - banging my head against a wall

oops