PDO statements not executing?

Question

I'm trying to use PDO (php data object) to execute queries in a .php file like so:

global $db, $table;
$sth = $db->prepare('INSERT INTO $table(user, timerun, magexp, crimsons, blues, golds, greens) VALUES (:user,:timerun,:magexp,:crimsons,:blues,:golds,:greens) ON DUPLICATE KEY UPDATE timerun=timerun+:timerun, magexp=magexp+:magexp, crimsons=crimsons+:crimsons, blues=blues+:blues, golds=golds+:golds, greens=greens+green');
$sth->execute(array(':user' => $user, ':timerun' => $timerun, ':magexp' => $magexp, ':crimsons' => $cr, ':blues' => $bl, ':golds' => $go, ':greens' => $gr));
echo "success";

However, it doesn't actually update my table. I don't get an error or anything.

Am I doing something wrong or is PDO not supported? The PDO docs said "Beware: Some MySQL table types (storage engines) do not support transactions. When writing transactional database code using a table type that does not support transactions, MySQL will pretend that a transaction was initiated successfully. In addition, any DDL queries issued will implicitly commit any pending transactions."

I'm fairly certain my MySQL tables do support transactions, because the regular 'mysql_query' does work.

Thanks.

You're not using transactions. If you were, you would have to be using table types that support them. You should also start/stop transactions using the built-in PDO methods. — Brad, Aug 14 '12 at 17:02
Oh.. okay. But then why is my query not executing? I know it can make the connection to the database so I don't see the problem. ;/ — phl0w, Aug 14 '12 at 17:04
Is the last parameter a typo? `greens=greens+green` - I think it should be `:greens`? — andrewsi, Aug 14 '12 at 17:04
Thank you for noticing that, Andrewsi. It indeed was a typo and I have fixed it now, but unfortunately that didn't fix the statement not executing ;/ — phl0w, Aug 14 '12 at 17:07

Luke Adamczewski · Accepted Answer · 2012-08-14T17:23:57.770

1

I'm not sure about Your code, You have variable inside single quoted string it will not work, You should use double quotation like this:

global $db, $table; 

$sth = $db->prepare("INSERT INTO $table(user, timerun, magexp, crimsons, blues, golds,  greens) VALUES (:user,:timerun,:magexp,:crimsons,:blues,:golds,:greens) ON DUPLICATE KEY   UPDATE timerun=timerun+:timerun, magexp=magexp+:magexp, crimsons=crimsons+:crimsons, blues=blues+:blues, golds=golds+:golds, greens=greens+green:"); 

$sth->execute(array(':user' => $user, ':timerun' => $timerun, ':magexp' => $magexp, ':crimsons' => $cr, ':blues' => $bl, ':golds' => $go, ':greens' => $gr)); echo "success";

For security:

First of all i would create some associative array with all possible tables from project as keys and then check if table from variable exists as array index using if(isset($validTables[$table])) and then continue the query. For example

<?php
  $validTables = array('foo' => true, 'bar' => true, 'other' => true);
  if(isset($validTables[$table])) 
  {
    // query logic here
  }
  else throw new Exception(sprintf('Security error %s table not exists', $table));

Check this code because i wrote it without parsing with php

edited Aug 14 '12 at 17:23

answered Aug 14 '12 at 17:04

Luke Adamczewski

395
3
14

Better to not use dynamic tables at all! This is subject to SQL injection. – Brad Aug 14 '12 at 17:04
Sure but i'm only pointing his syntax error the rest should by handled by himself – Luke Adamczewski Aug 14 '12 at 17:07
Thank you, that fixed it! However, how would I stop the SQL injection vulnerabilities? I actually swithced to using PDO because I was told it'd keep the vulnerabilities away ;/. – phl0w Aug 14 '12 at 17:10
@phl0w, if someone tricks your app into setting `$table` to something other than simply a table name, you'll execute that illicit code. PDO supports query parameters which are a useful substitute for dynamic *values* in an SQL query, but parameters don't work for table names, column names, expressions, SQL keywords, etc. – Bill Karwin Nov 20 '13 at 21:56
@phl0w, see my presentation [SQL Injection Myths and Fallacies](http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies) for more tips. – Bill Karwin Nov 20 '13 at 21:57

PDO statements not executing?

1 Answers1