Status: Solved
Priority: Medium
Security: Public
Views: 118
Last Modified: 2016-09-07

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.

rwlloyd71

Asked: 2016-09-06

Advertise Here

1 Solution

LVL 63

Julian HansenCommented: 2016-09-07

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.

rwlloyd71Author Commented: 2016-09-07

Thank you, good bit of logic!

LVL 63

Julian HansenCommented: 2016-09-07

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

LVL 63

Julian HansenCommented: 2016-09-07

You are welcome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Parameterised queries

Join & Write a Comment Already a member? Login.

Featured Post