• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
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.
  • 3
1 Solution
Julian HansenCommented:
Can you not do something like this in your where clause
  (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.
The above would obviously require passing the same variable in twice each.
rwlloyd71Author Commented:
Thank you, good bit of logic!
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);

Open in new window

Julian HansenCommented:
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.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now