Building a PDO MYSQL query from a php arrays?

Question

I am trying to build a query from the values of an array so far I have,

$itemPlus = $_POST['itemPlus'];
$query = implode(', ', array_map(function($items) {return $items . ' = ' . $items . '-?';}, $items));
// $query = 'hats = hats-?, gloves = gloves-?, scarfs = scarfs-?'
$params = implode(', ', $userCost);
// $params = '10, 7, 9'
$q = $dbc -> prepare("UPDATE items SET " . $query . ", $itemPlus = $itemPlus+1 WHERE id = ?");
$q -> execute(array($params, $account['id']));

It doesn't work, this is my first time trying this and as it doesn't work I am obviously doing something wrong!?

Thanks

Your code is vulnerable to SQL injection. Please don't use $_POST variables (or any other user input) directly in your query. — FtDRbwLXw6
– FtDRbwLXw6, Commented Jan 24, 2012 at 19:45
@cgwebprojects: The $itemPlus variable is injected directly into the query string, and $itemPlus is just $_POST['itemPlus']. — FtDRbwLXw6
– FtDRbwLXw6, Commented Jan 24, 2012 at 19:51

Michael Berkowski · Accepted Answer · 2012-01-24 19:51:32Z

2

Since $params is a string of values, you cannot make it into an array along with $account['id']. Instead.use the array that created it $userCost:

// Start with the $userCost array...
$paramArr = $userCost;
// Add $account['id'] to it
$paramArr[] = $account['id'];
// And pass that whole array to execute()
$q -> execute($paramArr);

Since $itemPlus is coming from $_POST, you will need to be sure that is valid input. Since it refers to a column name, it is recommended to use a whitelist for that:

// Check against an array of possible column names:
if (!in_array($_POST['itemPlus'], array('col1','col2','col3','col4',...)) {
   // $_POST['itemPlus'] is NOT VALID
   // Don't proceed...
}

edited Jan 24, 2012 at 19:51

answered Jan 24, 2012 at 19:46

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cgwebprojects Over a year ago

Thanks Michael, I can see now why it didn't work and it is now thank you.

Michael Berkowski Over a year ago

@cgwebprojects Please also see the part I just added about validating itemPlus with a whitelist of possible column values...

cgwebprojects Over a year ago

Wouldn't the query just fail if thr column was not found?

Michael Berkowski Over a year ago

@cgwebprojects Yes in some cases it will fail, but it also leaves you open to SQL injection if a malicious value is passed into $_POST.

FtDRbwLXw6 · Accepted Answer · 2012-01-24 19:47:52Z

1

Your problem (one of them) lies here:

$q -> execute(array($params, $account['id']));

The $params variable is a comma-delimited string:

// $params = '10, 7, 9'

You want to pass an associate array of params and values to the execute() method, like this:

$params = array(
    'hats-?' => 10,
    'gloves-?' => 7,
    'scarves-?' => 9
);

// ...

$q->execute($params);

answered Jan 24, 2012 at 19:47

FtDRbwLXw6

29.1k16 gold badges74 silver badges108 bronze badges

Collectives™ on Stack Overflow

Building a PDO MYSQL query from a php arrays?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related