php - PDO insert, foreach($_POST)

Question

My code below will not insert into my database. I do not know where my misstake is being made. (Thanks for the notifications regarding sql injections, will read about that laters <3)

This is my php code so far:

$sqlArray = array();
$nameArray = array();
$valueArray = array();

foreach($_POST as $name => $value) {
    //$sqlArray[] = "':".$name."'=>$".$name;
    $nameArray[] = $name;
    $valueArray[] = "'".$value."'";
}

$names = implode(', ', $nameArray);
$values = implode(', ', $valueArray);

$sql = "INSERT INTO random ( ".$names." ) VALUES ( ".$values." )";


$addRandom = $dbh->prepare( $sql );
$addRandom->execute();

And the output by $sql looks like:

INSERT INTO random ( random1, random2, zipCode) VALUES ( 'Namn', 'Adress', 'Zipcode' )

What should I change or add?

Answer 1

You are already using the PDO library, which is good for starters, however you aren't exactly utilizing the communication method as it would be adequate:

$sqlArray = array();
$nameArray = array();
$valueArray = array();

$insertSQL = "INSERT INTO random ([[tablename]]) VALUES (?);";

$whiteList = array(
    'random1',
    'random2',
    'zipCode',
    ...
);

function whiteListedColumn($whiteList, $columnName){
    if (in_array($columnName, $whiteList)){
         return true;
    }

    return false;
}

function prepareStatement($dbHandler, $templateSQL, $columnName){
    $completeSQL = str_replace('[[tablename]]', $columnName, $templateSQL);
    return $dbHandler->prepare($completeSQL);
}

try{
    foreach($_POST AS $name => $value) {
        if (whiteListedColumn($whiteList, $name)){
           $prepStmt = prepareStatement($dbh, $insertSQL, $name);
           $prepStmt->execute(array($value));
        }
    }
}catch(Exception $e){
    echo "Error has occured while inserting data.";
}

I've refactored the insert query to incorporate a wild-card binder which we will be using at the execute step (passing in an array of values to be bound to the appropriate places in the query indicated by ? marks).

You are passing in the colum names, so to sanitize them, we aren't going to take the route of manually escaping any bad characters, but we will take the route of comparing the input to a whitelist of accepted column names predefined - that way, anything that is 1) not threatening the consistency of your database, 2) semantically valid for your database will be allowed, everything else will result in the execute portion absolutely neglected.