0

I'm trying to make code insert data into MYSQL from arrays
my problem is, it's not protected against SQL Injection.
I searched at every where ,How can i prevent it compeletly.
I looked for this question
How can I prevent SQL injection in PHP?
but i found two answers make me rethinking again.
https://stackoverflow.com/a/8255054/6523558

Every answer here covers only part of the problem.
In fact, there are four different query parts which we can add to it dynamically a string a number an identifier a syntax keyword.and prepared statements covers only 2 of them

https://stackoverflow.com/a/60442/6523558

I looked around for something will help me and this what i found
http://www.w3schools.com/sql/sql_injection.asp
http://www.w3schools.com/sql/sql_datatypes_general.asp

But nothing helped me to prevent it completely from my code.

I'm using this code to insert array data to MYSQL.
It's prevent it by using base64. 

$tbname = "some_table";
$array1 = array("one"=>"1a","two"=>"2b");
$S1["add1"] = " (";
$S1["add2"] = " VALUES (";
foreach($array1 as $k1=>$n1){
if($n1 !== ""){
$S1["add1"] .= $k1 . ", ";
$S1["add2"] .= "'" . base64_encode($n1) . "', ";
};}
$S1["add1"] = substr($S1["add1"],0,-2);
$S1["add1"] .= ")";
//if($S1["add1"] == ")"){$_SESSION["sql_msg"] = "You have to put at least one input";} else {
$S1["add2"] = substr($S1["add2"],0,-2);
$S1["add2"] .= ")";
$sql = "INSERT INTO " . $tbname . $S1["add1"] . $S1["add2"];
//if ($conn->query($sql) === TRUE) {$_SESSION["sql_msg"] = "New record created successfully";
//} else {$_SESSION["sql_msg"] = "Error: " . $sql . "<br>" . $conn->error;};}
//ref1();

echo $sql;
Community
  • 1
  • 1
Aidb David
  • 11
  • 6
  • Use parameterized queries. Put placeholders for the values and check the column names with a whitelist. – chris85 Jan 21 '17 at 16:20
  • Never use base64, it makes no sense. You should whitelist your table and field names. Look here, [An SQL injection against which prepared statements won't help](https://phpdelusions.net/pdo/sql_injection_example) – Your Common Sense Jan 21 '17 at 16:22
  • As i said `Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to it dynamically a string a number an identifier a syntax keyword.and prepared statements covers only 2 of them` , and beside this my code is MYSQLi , i don't want to change hunderts of lines , and i don't want to put blacklist, but thank you for this new information @YourCommonSense – Aidb David Jan 21 '17 at 16:43
  • @AidbDavid a) whitelists aren't blacklists. b) heard of "search and replace"? c) the principle of injection and protection is the same for PDO and MYSQLi. d) your code looks like it would do very well with a complete refactoring as is. – Franz Gleichmann Jan 21 '17 at 16:49
  • @FranzGleichmann a)whitelists what is allowed b) I can't replace something like an Adress for something or url_link or some names in other languages c) I know and this what i need, i need someone help – Aidb David Jan 21 '17 at 17:07

2 Answers2

2

Based on my article (which is more focused on disclosing bad and wrong practices), An SQL injection against which prepared statements won't help

The protection from SQL injection is actually simple, and can be formulated in just two statements:

  • use placeholders for the every data value
  • whitelist everything else

Given all that, you should

  1. whitelist your table and field names
  2. create a query consists placeholders and filtered out table and field names
  3. send your variable array into execute.

For this purpose first define an array with all the allowed field names

$allowed = ["one","two"];

Then out of this array you will need to to create a SET statement for the INSERT query that should look like

one = :one, two = two:

For this you need a code like this

$allowed = ["one","two"];
$params = [];
$setStr = "";
foreach ($allowed as $key)
{
    if (isset($array1[$key]))
    {
        $setStr .= "`".str_replace("`", "``", $key)."` = :".$key.",";
        $params[$key] = $_POST[$key];
    }
}
$setStr = rtrim($setStr, ",");

Note that we are also getting data values into distinct array

Finally, get your query from parts (given a table name is already hardcoded in you script) and then prepare and execute it using array with values

$tbname = "some_table";
$sql = "UPDATE `$tbname` SET $setStr";
$pdo->prepare($sql)->execute($params);
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • in my code everything is variable from the connection and host till columns and number of variables, but thx for your answer many people will need it – Aidb David Jan 21 '17 at 16:55
0

I guess the best way is by using
$sql = str_getcsv($sql,";")["0"];
before the execution to prevent any extra commands

Aidb David
  • 11
  • 6