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
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.
//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
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you not do something like this in your where clause
EDIT:
The above would obviously require passing the same variable in twice each.
WHERE
(LEN(?) = 0 OR tblmembers.firstname = ?) AND
(LEN(?) = 0 OR tblmembers.surname = ?) AND
(LEN(?) = 0 OR and tblaccounts.postcode =?) AND
(tblcards.primarycard = 'primary')
EDIT:
The above would obviously require passing the same variable in twice each.
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);
Premium Content
You need an Expert Office subscription to comment.Start Free Trial