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.
Solved
Parameterised queries
Posted on 2016-09-06
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.
[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
Active today
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.
Active today
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);
Featured Post
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.
Suggested Courses
Course of the Month5 days, 13 hours left to enroll
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.