3

For my database abstraction I want to exclusively rely on PDO parameter markers when using data literals in prepared statements, in order to eliminate the need for escaping and quoting.

The problem is that this seems to work only for some query types: The obvious SELECT, UPDATE and INSERT queries work as expected but CREATE TABLE throws a "General error", regardless of whether I use named or question mark markers.

<?php

// works:
(new PDO('sqlite::memory:'))->prepare('CREATE TABLE test (name TEXT DEFAULT \'unnamed\')')->execute();

// doesn't work:
(new PDO('sqlite::memory:'))->prepare('CREATE TABLE test (name TEXT DEFAULT ?)')->execute(['unnamed']);

This is the error:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 1 near "?": syntax error in /home/andy/projects/orm/public/pdo.php:7
Stack trace:
#0 /home/andy/projects/orm/public/pdo.php(7): PDO->prepare()
#1 {main}
  thrown in /home/andy/projects/orm/public/pdo.php on line 7

The important difference to Can PHP PDO Statements accept the table or column name as parameter? is that I am not trying to parameterise identifiers but a literal.

If parameters cannot be used in DDL, can someone point me to some docs or specs please?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Andreas Piening
  • 31
  • 2
  • This must be a limitation of SQLite because it's possible in MySQL/MariaDB – Dharman Dec 12 '22 at 12:06
  • @Dharman for [me it doesn't seem so](https://phpize.online/sql/mysql57/undefined/php/php81/a172186064acb0164514c8b3ab56b61f/). Only when emulation is enabled it expectedly worked for me. What I am overlooking? – Your Common Sense Dec 12 '22 at 13:06
  • @YourCommonSense Ahh, ok. Only MariaDB supports it then. – Dharman Dec 12 '22 at 13:08

2 Answers2

2

According to this clarification, a query parameter (QPs) can not be used in place of any literal value. In particular, while SQLite diagrams presently indicate that QPs can be used in the DEFAULT clause, the dedicated section explicitly prohibits it.

PChemGuy
  • 1,582
  • 3
  • 6
  • 18
1

It looks like you cannot. Although some databases may allow it e.g. MariaDB.

SQLite does not allow for CREATE TABLE statement to contain parameters. The reason for this is explained by Stephan Beal on sqlite.org forum:

Unlike most databases, sqlite does not store a db schema in some internal/idealized form. It stores the schema as the raw text the user gives it. That makes it impossible for it to support bound parameters in CREATE TABLE.

As you probably are aware prepared statements do not substitute the value into the expression. Prepared statements use the bound value during execution. If the CREATE TABLE statement is not executed into an internal form by SQLite, then the parameter is never used. This means that the CREATE TABLE statement cannot contain any variables. It may contain constant values and expressions in the DEFAULT clause, but they have to be part of the statement and cannot be provided as parameters.

As for why other databases do not allow DEFAULT values to be parameterized is a different story. The DEFAULT clause has actually a dual syntax - it can accept a data literal or an expression. For this reason, some RDBMSs may not allow this to be parameterized.

Dharman
  • 30,962
  • 25
  • 85
  • 135