Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parameterised queries

Posted on 2016-09-06
4
Medium Priority
?
109 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 60

Accepted Solution

by:
Julian Hansen earned 2000 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 60

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 60

Expert Comment

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
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…
Suggested Courses

886 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