0

We are all familiar with traditional form processing i.e.

$email = $_POST['email']; $name = $_POST['name'];

etc.. and then we go ahead and get all the variables from a post. and then we would create a compound statement like 

$qry = "INSERT INTO $tableName (email,name) values ('$email','$name')";

Now what if you had like 18-20 questions? most people would just write lines and lines of code 99.9% of everyone online does it the same way over and over again.

Let's try something different shall we? I realized there must be a better way using arrays.

For years I've been looking for a simple routine and looked everywhere for it that will CRAFT an insert statement FROM all the $_POST variables.

It dawned on me that $_POST is actually an array so I wrote this little script:

$vars = $_POST;
print_r($vars);
exit;

After working thru this for a few hours with people on this forum here is the resulting code. I believe that by creating a checksum of the hash of all the array keys will solve the fears of SQL attacks, since the server isn't called unless it gets an exact match. If anyone adds a field it will fail. Does everyone agree?

$predefinedChecksum = "84e602bbec8124f298e353171fb7f5b2"; // this is the hash value of all the array keys
$keys = array_keys($_POST);
$values = array_values($_POST);
$sql = "INSERT INTO $tableName (" . join(',', $keys) . ") VALUES ('" . join("',", $values) . "');";
$checksum = md5(join(',',$keys));
if ($checksum<>$predefinedChecksum) exit;
else $res = mysql_query($qry, $conn);

Thanks to all who contributed... I think we've got the workings of a great script.

Someone mentioned to unset the 'button' - how do you do that?

unset( $_POST['button'] );

This did not work - the output of the script still shows 'button' as one of the variables. So the output of the script still has a field called 'button' in the end.

I'm not sure how you could remove it from the series of $values Anyone have ideas?

Also the output

INSERT INTO (nameFirst,nameLast,emailPref,emailAlt,phoneDay,phoneMobile,ethnicity,yob,income,marital,kids<18,Education,employment,company,title,industry,department,revAnnual,numemps,street,city,state,zip,Type_Mobile,tablet,computer,laptop) VALUES ('Vik',Grant',viktor@eml.cc',',',',african',',19',single',',Some_HS',student',',',Finance_Accntg',Admin',',',',',',',Android',',',');

is missing the ' quote mark on the beginning of the value - can a join exist as join (a,b,c)?

Viktor
  • 517
  • 5
  • 23
  • i suggest to use database class like [Zend_Db_Adapter](http://framework.zend.com/manual/1.12/en/zend.db.adapter.html) – Peter May 29 '14 at 09:48
  • 1
    I would be VERY wary of SQL injection if you're trying to find a way of getting your `$_POST` variables straight into an insert statement... – Pudge601 May 29 '14 at 09:49
  • I wanted to do something like this too. This is what I eventually followed : http://stackoverflow.com/questions/11611631/best-practice-store-large-form-values-into-database It's a good solution – asprin May 29 '14 at 09:52
  • Good point. Let's first see if anyone in this forum can write an INSERT statement. I'm not profient enough in PHP to write that INSERT staement. Then we can worry about protecting it. The way I'd protect it from SQL attacks is to checksum the field names and the compare that checksum against the correct. For sake of speed (and the whole point of this question) - is to provide the Online community with a piece of code that everyone can use anytime they don't feel like specifying EXACT field names. – Viktor May 29 '14 at 09:57
  • So above I made some changes and a checksum. This checksum is hard-coded into the script and compared against the actual checksum of the field names. This should prevent any SQL injection attacks. If someone tries to add a field it simply exits. Will that solve the fears surrounding SQL injection? – Viktor May 29 '14 at 10:53
  • Please be aware that the mysql extension (supplying the mysql_ functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Oldskool May 29 '14 at 12:17

4 Answers4

0

Just loop it with foreach http://nl1.php.net/manual/en/control-structures.foreach.php be careful as this allows any column to be overwritten. It is safer to specify which columns are allowed to be inserted.

And plz use something like PDO to use prepared statements

Chris
  • 8,168
  • 8
  • 36
  • 51
  • Read above. The right way is to do a for_each loop - i simply don't know how to write that loop in such a way that it does specify the columns properly $qry = "INSERT INTO $tableName (email,name) values ('$email','$name')"; also this is an INSERT statement not an UPDATE statement. – Viktor May 29 '14 at 09:52
0

you shouldn't do this since its huge security issue.

But if you really want to do this: (untested, you can still inject SQL so this ain't secure!)

$keys = array_keys($_POST);
$values = array_values($_POST);

$sql = "INSERT INTO $tableName (" . join(',', $keys) . ") VALUES ('" . join("',", $values) . "');";

Edit:

If you are using PDO, you could do it like this:

$keys = array_keys($_POST);
$values = array_values($_POST);

$valuePlaceholders = "";

for ($i=0; $i < count($_POST); $i++) {
    $valuePlaceholders .= $i === 0 ? '?' : ', ?';
}

$sql = "INSERT INTO $tableName (" . join(',', $keys) . ") VALUES ($valuePlaceholders);";

And when executing $pdo->execute($values);

Janne Savolainen
  • 125
  • 5
  • Lol..never knew `join` was an alias of `implode`. Also you might wanna consider unsetting the submit button key – asprin May 29 '14 at 09:54
  • Let me test this - so far its the best idea. Guys - its so simple to secure this from SQL injection using a MD5 hashtag. Once I find it works I'll post the code. – Viktor May 29 '14 at 10:29
  • It works! So to solve the SQL injection problem what I did was this: $checksum = md5(join(',',$keys)); print $checksum; Each unique form has a set of values and that generates a checksum. I hard-code that checksum into the form. As long as the checksum matches the form submits just fine - if it mismatches then we know it was a SQL injection and it simply exits the script. Thank you Janne - that worked beautifully The first line of the script will compare the – Viktor May 29 '14 at 10:38
  • Very close - the SQL statement won't submit because the values need quotation marks. any ideas? – Viktor May 29 '14 at 10:45
  • Oh, sorry. That was my mistake. Change this `VALUES (" . join(',', $values) .");` to `VALUES ('" . join("',", $values) . "');` – Janne Savolainen May 29 '14 at 10:50
  • 1. It's impossible to set NULL this way. – DarkSide May 29 '14 at 10:53
  • 3. MD5 hashtag is no big use in this case and gives you nothing. Same results you can accomplish when check for only "good keys" on server side before inserting values in DB. In simple words - MD5 should be generated on server side to avoid hackers of your form. How you can generate that md5 on server if you don't know keys in advance? Right, you have to know your keys and if you do, then you can simply check for only allowed keys when user POSTs his form. No need for MD5 in result. – DarkSide May 29 '14 at 10:58
  • 1. True, sql should be created with loops to be able to set NULL. 2. Of course theres still way to inject SQL into values. Like I said at answer, this is security issue. – Janne Savolainen May 29 '14 at 10:58
  • You generate the MD5 once before launching the form. Hard code it into the script then until you change the form, the hash tag will remain the same. The point of this is the resulting form will have 30-50 questions and will be changing all the time. We don't want to keep changing field names and query names. So there is a need for MD5 - to compare whether someone added a field that wasn't supposed to be there in the $_POST array Still - the script isn't finished because the formatting isn't right - without quotes on the values on both sides it won't INSERT – Viktor May 29 '14 at 12:29
  • I updated my answer to add quotes on values. Did you try that? – Janne Savolainen May 29 '14 at 12:31
0

You don't really want to do this since you generate queries which can be altered by the client.

But to answer your question, you can do something like:

$columns = array("email", "name", "etc.."); // Array with the "good" columns.

// Unset the columns you do not want in your query.
foreach($_POST as $key=>$value){
    if(!in_array($key, $columns)){
        unset($_POST[$key]);
    }
}

$qry = "INSERT INTO " . $tableName . " (" . implode(", ", array_keys($_POST)) . ") values (" . implode("', '", array_values($_POST)) . ")";
user1961685
  • 80
  • 1
  • 9
  • Actually we do want to generate queries which can be altered by the client that's the whole point of the exercise. I can overcome the security issues with a MD5 checksum of the field name columns. For now, let's not worry about security, let's just see if its possible to construct an insert statement that does what the question asked. Thanks! – Viktor May 29 '14 at 10:07
0

Although the normal way is to use a loop for producing the string containing your values, i sometimes do the following when i know the exact order of keys in my array:

$arr = array(
   "email"=>"foo@bar.gr",
   "name"=>"vlzvl"
);
$sql = "INSERT INTO mytable (email,name) VALUES ('".implode("','",$arr)."')";
  • yes - this is good - and what I was seeking to do was NOT define an array in PHP - the array and the field names are defined in the FORM itself. aka its responsive. I like what you wrote however it once again isn't pulling the field names from the array - you define email,name – Viktor May 29 '14 at 10:06