Running queries in PDO without binding

Question

Can you run queries in PDO without preparing them? I am aware of the SQL-Injection issues that can arise with this but I am in a test environment.

I want to be able to write pure MySQL queries and just execute them, not have to prepare the query, bind the placeholders etc...

I would like to be able to execute a query like the following instantly.

INSERT INTO table (table_id, car, bike, date) VALUES (1, 'bmw', 'suzuki', 2004)

I seem to be getting errors running execute() directly on this query.

Thanks in advance.

Answer 1

The idea of prepared statements is not primarily that you can bind parameters, but that you can reuse the compiled statement multiple times, which should increase efficiency.

The prepare-execute workflow isn't too inconvenient for one-off use cases, but PDO offers other methods as well:

• exec executes a statement and returns the number of affected rows. It is useful for initialization stuff, but not for SELECTs.
• query is useful for static queries that don't involve untrusted input. It is similar to prepare-execute, but does not allow parameters, and does not allow the reuse of the compiled query.

Due to these limitations, they should generally only be used on static queries (i.e. the query is a plain string and not constructed from concatenations with variables).

You can safely escape user input with the quote method, so you could do something like

// untrusted data:
$car = 'bmw';
$bike = 'suzuki';
$year = 2004;
...
$dbh->exec('INSERT INTO table (table_id, car, bike, date) VALUES (1, '. $dbh->quote($car) .', '. $dbh->quote($bike) .', '. $dbh->quote($year) .')');

But this is so inconvenient that you'll end up using

$dbh->prepare('INSERT INTO table (table_id, car, bike, date) VALUES (1, :car, :bike, :year)')
    ->execute(array(':car' => $car, ':bike' => $bike, ':year' => $year));

instead.

Answer 2

Don't use a PDOStatement just use the PDO connection object directly.

$conn = new PDO("....");
$result = $conn->exec("INSERT INTO table (table_id, car, bike, date) VALUES (1, bmw, suzuki, 2004)");

or

$result = $conn->query("SELECT * FROM table");

http://www.php.net/manual/en/pdo.query.php