Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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
Nothing in an HTTP request can be trusted, including HTTP headers and form data. A form token is a tool that can be used to guard against request forgeries (CSRF). This article shows an improved approach to form tokens, making it more difficult to…
There are times when I have encountered the need to decompress a response from a PHP request.
This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing.
Set up your basic HTML file. Open your form tag and set the method and action attributes.:
(CODE)
Set up your first few inputs one for the name and …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 4 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.