Solved

Use $_POST variable as SQL parameters

Posted on 2016-09-09
16
29 Views
Last Modified: 2016-09-10
Hi


I am trying to work out a way of combining post variables with a parameterised query.


At the moment I gather the post variables, store them in their various keys, then feed the values into a parameterised SQL with an array containing the same variables in the same order as the fields within the query.


All fairly standard I think.


The problem is that I am forever having to write code to read and write out the variables, add new variables when the SQL changes and make sure  the number of parameters in the array matches the the required parameters in the SQL.

Is there a way that I can use a $_POST variable (holding all the posts) as the SQL parameter array, with the array keys defining which parameter the SQL requires, so all I would need to include in my code is something like...

$sql= "Insert into table (field1, field2, field3, field4) values (?, ?, ?, ?)";
$params=$_POSTS
$result=sqlsrv_query($conn, $sql, $params);

Any comments would be appreciated.

Thank you
0
Comment
Question by:rwlloyd71
  • 7
  • 6
  • 3
16 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41791759
Short answer: Probably yes, you can do this.  Longer answer: You need to sanitize the external data in the POST-method request.  PHP does not do that because it's considered a custom requirement for each individual input.  And that means filtering, validating, etc.  I'll try to show you a code example in a few minutes.
1
 

Author Comment

by:rwlloyd71
ID: 41791788
Thanks Ray
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41791802
See if this makes sense.  I cannot test it because I do not use Windows.  Hopefully the code and comments will make sense; if not please post back and I'll try to clarify my thinking.  I believe the $clean_post array will work as written, but it might be necessary to pass only the array_values() -- that should be tested.
<?php // demo/temp_rwlloyd71.php
/**
 * https://www.experts-exchange.com/questions/28968827/Use-POST-variable-as-SQL-parameters.html
 *
 * http://php.net/manual/en/function.sqlsrv-query.php
 */
error_reporting(E_ALL);


// A MOCK SANITIZER CLASS
Class Sani
{
    public function setKey($x)
    {
        $this->key = $x;
    }
    public function setValue($x)
    {
        $this->value = $x;
    }
    public function sanitize()
    {
        $this->error = FALSE;

        // RULES HERE, ACCORDING TO $this->key
        // SET $this->error ON FAILURE
        // RETURN TRUE IF $this->value IS OK FOR $this->key

        return TRUE;
    }
    public function getKey()
    {
        return $this->key;
    }
    public function getValue()
    {
        return $this->value;
    }
    public function getError()
    {
        return $this->error;
    }
}


// IF THERE IS A REQUEST, SHOW THE REQUEST
if (empty($_POST)) goto showForm;
ob_start();
echo '<pre>';
var_dump($_POST);
echo PHP_EOL;

// REMOVE UNWANTED ELEMENTS OF THE REQUEST
unset($_POST['noise']);

// SANITIZE THE REQUEST VARIABLES
$clean_post = [];
$sanitizer = new Sani;
foreach ($_POST as $key => $value)
{
    $sanitizer->setKey($key);
    $sanitizer->setValue($value);
    if ($sanitizer->sanitize())
    {
        $clean_post[$sanitizer->getKey()] = $sanitizer->getValue();
    }
    else
    {
        trigger_error($sanitizer->getError(), E_USER_WARNING);
    }
}

// CONSTRUCT THE QUERY
$qmarks = array_fill(0, count($clean_post), '?');
$qmarks = implode(', ', $qmarks);
$names  = array_keys($clean_post);
$names  = implode(', ', $names);
$query  = "INSERT INTO table ( $names ) values ( $qmarks )";
$parms  = $clean_post;

// SHOW WHAT WE ARE WORKING WITH
echo PHP_EOL;
var_dump($query);
echo PHP_EOL;
var_dump($parms);

// RUN THE QUERY HERE
// $result = sqlsrv_query($conn, $query, $parms);

echo '</pre>';


showForm:
$req = ob_get_clean();


// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />

<style type="text/css">
/* STYLE SHEET HERE */
</style>

<script src="https://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
    /* jQUERY HERE */
});
</script>

<title>HTML5 Page With jQuery in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<p>$req</p>

<form method="post">
<input name="one" />
<br>
<input name="two" />
<br>
<input name="noise" type="submit" />
</form>

</body>
</html>
HTML5;


// RENDER THE WEB PAGE
echo $htm;

Open in new window

0
 

Author Comment

by:rwlloyd71
ID: 41791899
Ray

Thank you again for your continued help.

I have set up your code at https://smartloyalty.co.uk/testapp/test.php

The code does exactly what I was hoping for.

I have added to your code to include submitting the query to a test database and hosted at https://smartloyalty.co.uk/testapp/test1.php

You can see the code at https://smartloyalty.co.uk/testapp/test.txt

Unfortunately your code does not seem to add the record to the database. Can you see if I have missed anything?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41791913
Please add some code to check the return values, and visualize the error messages, if any, from the various SqlSrv functions.   Have you verified that the table exactly matches the HTML form, with respect to the column names matching the input control names?
0
 

Author Comment

by:rwlloyd71
ID: 41792016
Ray

I think I hve found the error when you submit the insert query:

            MESSSAGE: String keys are not allowed in parameters arrays.

the parameter array that fail is:

array(4) {
  ["one"]=>
  string(1) "1"
  ["two"]=>
  string(1) "2"
  ["three"]=>
  string(1) "3"
  ["four"]=>
  string(1) "4"
}

One that works is:

Params: array (
  0 => 'BLUEDI',
  1 => 'BLUEDI-A-00012599',
  2 => 'BLUEDI-M-00012598',
  3 => 'LYBLA999905X',
)

So we have to use a straight array and not an associative array, which mean that we have to get the order of the parameters just right by ordering the form fields in line with th sql fields!

I am correct, or is there a way round this
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41792027
You're correct.  This means that the order of the form inputs in the PHP script is in play.  We can remove the keys with array_values() but that creates a dependency on the HTML document.  This is an expedient solution, but there might be a more "computer scientific" answer if we created an array of the expected input name= attributes, placing the names in the order that we want to use to match the question marks in the query template.  This would let us decouple the order of the $_POST inputs from the query, and we could establish the query order independently of the HTML document.

PDO is kind of stupid about this, too.  If that's any consolation ;-)
0
 

Author Comment

by:rwlloyd71
ID: 41792037
Ray

it is now working!

I used:

$params  = array_values($clean_post);

http://smartloyalty.co.uk/test app/test1.php

I'll sleep on your last comment, but I have found what I am looking for for now!

Thanks for all your help!

Richard
1
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rwlloyd71
ID: 41792042
Just dawned on me, could we sort the order of the names into alphabetical order, then sort the keys, which should be the same as the names, in to alphabetical order then use the array_values
0
 

Author Comment

by:rwlloyd71
ID: 41792043
Just dawned on me, could we sort the order of the names into alphabetical order, then sort the keys, which should be the same as the names, in to alphabetical order then use the array_values
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41792045
Yes, if the $_POST keys are the same as the table column names, we can sort them both.  Good idea.  If the keys are NOT the same as the table column names, you gotta can-o-worms.
0
 

Author Comment

by:rwlloyd71
ID: 41792069
I always code my names the same as my table.

the jobs a good'un!
1
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41792546
Just to add to Ray's comments above here is something I find very useful about PHP and which I use a lot to make PHP / SQL a bit easier.

In my form I declare my input names as arrays with the indices as the same names as the columns in my database
<input name="data[firstname]" />
<input name="data[surname]" />
<input name="data[email]" />
<input name="data[optionalfield]" />

Open in new window


Now when I ask for the input I get an array back
$data = isset($_POST['data']) ? $_POST['data'] : false;

Open in new window


If $data is false I can process the error - assume it has data.
Now check if it is an array, if it is we proceed otherwise we terminate
if (is_array($data)) { 

Open in new window


To create the insert query I do this
$fields = implode('`,`', array_keys($data));

$values = '';
foreach($data as $value) {
  $values .= "'" . mysqli->real_escape_string($value) . "',";
}
// GET RID OF THE EXTRA ,
$values = trim($values, ',');
$query = "INSERT INTO tablename (`{$fields}`) VALUES ({$values})";

Open in new window

Now if you update your table definition - all you do is change your form and the code does the rest.
The above is a simplistic example, it skips over  validation but it illustrates the general idea of using form data as arrays and using that to build a query.

EDIT
See comment on security below
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41792552
In addition to the above - there are security issues with the code that you need to be aware of.
Let's say that you have a user table with a field validated and admin
With the above a malicious person could modify the form in the browser to send through values for these fields as well - effectively creating a user account and activating it and making it admin.

The code above therefore still requires security checks on the data and should not simply allow everything and anything from the form to pass through to the database.

In my code I have an intermediary step that iterates through the array and removes anything from it I don't want.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41792582
Given that $_POST is an array, and always present in every scope and namespace, I don't get the advantage of making its elements into arrays, too.  You can just use what is in $_POST -- easy!

I completely get the idea of security.  Information technology security is a full-time, four year college major.  In other words, it's complicated, nuanced, and often distracting from the issues at hand . One of my favorite conference presentations, by Barry Austin, was entitled "How to Make PHP Security Suck Less."  About the most I can advise anyone here in the narrow forum of E-E is filter and sanitize external inputs, because all external inputs are attack vectors.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41792599
Except when you have instances where your form might contain data that is not meant for the database - for instance a file to take a mundane example.

$_POST is global but I prefer not to work with it in that context - it makes it too difficult to maintain to go chasing over a code base to find all instances of $_POST when you change something - I prefer to abstract it and deal with it only one place.  With one line I can extract the data from the $_POST (which may be all of it - or not) and then send that off to wherever I need it to go. I can modify it and massage it but I always have the original $_POST to refer back to if needed.

It probably is much of a muchness in the end but it works for me.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP Script - Am I missing anything here? 8 39
php documentation 4 22
Filter wordpress query 3 14
PHP JSON Clean up 5 10
Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now