Solved

Parameterised queries

Posted on 2016-09-06
4
72 Views
Last Modified: 2016-09-07
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.
0
Comment
Question by:rwlloyd71
  • 3
4 Comments
 
LVL 54

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41787496
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
 

Author Closing Comment

by:rwlloyd71
ID: 41787501
Thank you, good bit of logic!
0
 
LVL 54

Expert Comment

by:Julian Hansen
ID: 41787510
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
 
LVL 54

Expert Comment

by:Julian Hansen
ID: 41787511
You are welcome.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question