Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parameterised queries

Posted on 2016-09-06
4
Medium Priority
?
107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 59

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 59

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 59

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

688 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