MYSQLでPDO::prepareを利用し、テーブル名が数値の場合動作しない

Question

sample_dbデータベースの中に1という名称のテーブルを設定しております。数値列でテーブル名を指定することはMYSQLでは禁じられていないと思います。ただ、SQL文を書くときにデータベース名を明示的に書かなければエラーになることがわかりました。

$sql = "SELECT * FROM 1 WHERE category_1 = 1 AND category_2 = 1 AND status = 1 AND ver = (SELECT MAX(ver) FROM 1)";

//Warning: PDO::query(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 WHERE category_1 = 1 AND category_2 = 1 AND status = 1 AND ver = (SELECT MAX(v' at line 1 in /Applications/MAMP/htdocs/textile/dist/51_class.php on line 38

このようにテーブル名を単純に1を指定するとエラーが出力されるため、以下のように明示的に書き換えると動作することが確認できました。

$sql = "SELECT * FROM sample_db.1 WHERE category_1 = 1 AND category_2 = 1 AND status = 1 AND ver = (SELECT MAX(ver) FROM sample_db.1)";

これを前提に、PDO::prepareを利用（テーブル名についてプレースホルダをおいて、値をバインドする）すると以下のようなエラーが出力されてしまいます。

$id = 1;
$id_sub1 = 2;
$id_sub2 = 3;
$sql = "SELECT * FROM sample_db.? WHERE category_1 = ? AND category_2 = ? AND status = 1 AND ver = (SELECT MAX(ver) FROM sample_db.?)";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1,$id);
$stmt->bindValue(2,$id_sub1);
$stmt->bindValue(3,$id_sub2);
$stmt->bindValue(4,$id);
$stmt->execute();
$result = $stmt->fetch();

//Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WHERE category_1 = ? AND category_2 = ? AND ver = (SELECT MAX(ver) FROM sample_db' at line 1 in /Applications/MAMP/htdocs/textile/dist/1_main.php:61 Stack trace: #0 /Applications/MAMP/htdocs/textile/dist/1_main.php(61): PDO->prepare('SELECT contents...') #1 {main} thrown in /Applications/MAMP/htdocs/textile/dist/1_main.php on line 61

sample_db.?のような指定の仕方がprepareステートメントでは認められないのか、Fatal errorの理由がわからず行き詰まってしまいました。どのように解決しうるか、アドバイスをいただけるとありがたいです。

【追記】
諸々記事を検索をしていたらテーブル名にはプリペアステートメントが利用できないようなものを見つけました。（例）解決策としてprepareを使わずにsql文に直接変数を入れるような方法ばかり見つかるのですが、インジェンクション対策的に受け入れられない方法かと思うのですが、DByaテーブルの構造から見直すしかないのでしょうか？

Answer 1

FROM sample_db.?

ご認識の通り、「値」にしか使えません。

、DByaテーブルの構造から見直すしかないのでしょうか？

そもそも入力値によってテーブルが指定されるという状況自体があり得ません。
また、マジックナンバー含めて「１」というテーブル名では役割が不明ですし、数値始まりの命名はあまりよしとされてないと思います（変数や関数名に数値始まりが許可されていないのと同じように）

• 役割が分かる、意味のある名前を付ける
• ユーザー入力により動的に指定されない

は必須かと。

Comments

[pegy]

ありがとうございます。
>役割が分かる、意味のある名前を付ける
本件については、理解できました。そのように対応いたします。

>ユーザー入力により動的に指定されない
この点について、少し悩ましい（実際にどのように設計すべきか）悩ましいと考えております。
便宜上、少し説明が適切ではなかったのですが、
①1のテーブルの意味はユーザー情報で、sample_db.?というような形でユーザー単位で分けれれたテーブルに振り分けようという発想でした。
②また、ユーザー入力値がsample_db.?にセットされるというわけではなく、実際には、$_SESSION['user_id']のような形で取得されid情報がセットされます。データを辿ればログイン時のユーザー入力値なのですが、$_SESSION['user_id']はログイン情報に基づいてMYSQLから取得されるもので、必ず数値型の値です。
③例えば1テーブルはユーザー情報と言っても、単純なユーザー属性だけではなく、ユーザーから受け付けたリッチテキストエディタの情報やそれが更新するたびに、レコードが追加されていくイメージで、一人のユーザーに対しても相当数のレコードが作成されていくことを想定しています。

特に③を前提にするとユーザー数 X レコード数を考慮すると相当数のレコードとなり（リッチテキストエディタのカラムの内容を条件にクエリを発行することはないのですが）、ユーザー単位でテーブルを分けることが素人ながら良いのかなと考えてしまいました。

ただ、おっしゃる通り、ユーザーの情報を管理しているという意味では1さんと2さんの違いだけでそのフラグをデーブル内で分けて管理しても問題はないと思っているのですが、このようなケースにおいても、やはり、一つのテーブルにまとめて管理すべきでしょうか？まとめずにテーブルを分ける方法しか素人なりに思いつかなかったのですが、分けるとしたら実務的・一般的に他の方法も考えらるますでしょうか？

重ねてのご質問、誠に申し訳ございませんがよろしくお願い申し上げます。

[m.ts10806]

その「ユーザー」が何と捉えているかによります。
teratailのような「利用者」であればテーブル1つで管理すべきですし、
「サービス契約者」のように、そもそもデータの混同が許されないものであればユーザーロール（DB）を分けるべきです。

[pegy]

なるほど、有難うございます。
少しサービス契約者やデータの混同がピンとこない部分があったのですが、例えばどのようなケースが考えられるものでしょうか？
なんとなくクレジットカード情報などを持っている場合、まんまん万が一でも、まちがっても他人のクライアントに表示されないために、上の階層のDBベースで、切り分けるようなイメージでしょうか？
もちろん利用者もメアドやパスワードといった重要情報もありますが、これはハッシュ化されているため、一定の安全性が確保できているのでという事かと推察しています

Answer 2

テーブル名が数値（数字列）というのは許可されていますが、下記のようにテーブル名をバッククォートで囲む必要があります。ちなみに、バッククォートで囲むのはMySQLの方言で、標準SQLではダブルクォートで囲むルールです。

SELECT * FROM `1` WHERE category_1 = 1 AND category_2 = 1 AND status = 1 AND ver = (SELECT MAX(ver) FROM 1)

次に、テーブル名にプレースホルダが使えないのは、「そういうルール」だからですが、そうなっている理由としては、プレースホルダの利用目的によると思います。プレースホルダは、Prepared Statement（準備された文）に値を与えるための構文ですが、Prepared Statementを使うと、SQL文をあらかじめコンパイルして、実行計画を割り当てるなどしておける（これが「準備」です）るのですが、テーブル名が可変だと、これらの「準備」が行いにくいというのがあると思います。

また、性能上の問題として、SQLは一つのテーブルに多数のデータ（行）があることを想定して設計されているので、ユーザ数だけテーブルができると性能が出にくく、ディスク使用量の無駄も大きくなると想像されます。

では、なぜ多数のテーブルができることを想定しないかということに遡ると、SQLはリレーショナルデータベースのクエリ記述言語ですが、リレーショナルデータベースは関係モデル（リレーショナルモデル）という理論に基づいているからです。テーブルをユーザ数ぶんだけ作る理論があってもよいとは思いますが、リレーショナルデータベースはそのようなものではないということが根本的な理由だと思います。