Parameterised queries

I have a query which is created depending on which variables are submitted via a form, an easy example might be:
 
//start the query
$sql= "select tblaccounts.companyid
                ,tblaccounts.accountno
                ,tblmembers.memberno
                ,tblcards.cardno
from tblaccounts
WHERE tblmembers.companyid = '$companyid' ";
 
//build where clauses based on the existence of some variables
if (strlen($firstname)>0){
$sql.= " and tblmembers.firstname = '$firstname' ";}
 
if (strlen($surname)>0){
$sql.= " and tblmembers.surname = '$surname' ";}
 
if (strlen($postcode)>0){
$sql.= " and tblaccounts.postcode = '$postcode' ";            }
 
// complete the query
$sql.= "  and (tblcards.primarycard = 'primary') order by surname, firstname,  postcode";
 
//pass the parameters
$params=array($companyid, $firstname, $surname, $postcode);
 
//run the query
$result=sqlsrv_query($conn, $sql, $params);
 
At the moment I can only run the query using the proper variables, not the ? parameters as the parameter array will change depending on the variables required.

This me open to sql injections and problems with apostrophes etc within variable etc

Is there a way that I can use an associative array to store the possible varaibles and call them within the sql?

I am coding in php 5.6, using mssql on a windows server.
rwlloyd71Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Can you not do something like this in your where clause
WHERE 
  (LEN(?) = 0 OR tblmembers.firstname = ?) AND
  (LEN(?) = 0 OR tblmembers.surname = ?) AND
  (LEN(?) = 0 OR and tblaccounts.postcode =?) AND
  (tblcards.primarycard = 'primary')

Open in new window

In other words lose the 'if' statements and let the query do the checking of the parameter fields - if they are blank then that sub-clause returns TRUE due to the OR otherwise it will check the field against the variable.
EDIT:
The above would obviously require passing the same variable in twice each.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwlloyd71Author Commented:
Thank you, good bit of logic!
0
Julian HansenCommented:
The other way to do it is

// Array to store parameters for query
$parameters = array();
if (strlen($firstname)>0){
  $sql.= " and tblmembers.firstname = ?";
  $parameters[] = $firstname;
}
 
if (strlen($surname)>0){
  $sql.= " and tblmembers.surname = ? ";
  $parameters[] = $surname
}

if (strlen($postcode)>0){
  $sql.= " and tblaccounts.postcode = ? ";    
  $parameters[] = $postcode;
}
...
// Prepeare statement here  (assuming PDO and $dbh already declared)
$sth = $dbh->prepare($sql);
$sth->execute($parameters);

Open in new window

0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.