SQLの中の変数が文字列だった場合の取得方法がわかりません

前提

PHPでPDO接続をしています。データベースはMAMPのMySQLを使用しています。PHPのバージョンは7.3.1です。

SQL文の中で変数を使用したときに、その変数が文字列だった場合の取得方法がわかりません

PHP
1$sql = 'SELECT id FROM plan';
2if(!empty($departure)) $sql .= ' WHERE departure = '.$departure;

$departureの中身は「大阪府」なのですが、文字列のためうまく表示されません。恐らくクォーテーションがついてないためかと思います。
このときの$sqlをデバックすると、「SELECT id FROM plan WHERE departure = 大阪府」です。

このため、$departureをクォーテーションで囲んでみたのですが、

PHP
1$sql = 'SELECT id FROM plan';
2if(!empty($departure)) $sql .= ' WHERE departure = '.'$departure';

$sqlをデバックすると、「SELECT id FROM plan WHERE departure = $departure」となり、
$departureが変数ではなく文字列として認識されてしまいます。

「SELECT id FROM plan WHERE departure = "大阪府"」のように正しく取得したいのですが、どうすればいいでしょうか。

退会済みユーザー

2020/01/04 07:33

phpからpdo接続する場合を想定した回答をしましたが、その場合は質問につけるタグを「php」「pdo」も添えていただければ幸いです。

Orlofsky

2020/01/04 07:57

さらに、同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。 https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html

mmmn

2020/01/04 08:19

初めての質問でしたのでタグの付け方が分からず、ご指摘いただきありがとうございました。回答いただいた内容で試しているのですがうまくいかず、改めて疑問点を明らかにしてから返信させていただきます。

行動規範の内容に同意します

回答2件

この回答は、phpからPDO経由で接続する事例になっていますが、
他の言語でも同様に扱えるものと思います。

SQL文を外部からくる文字列変数の連結で作ってはいけません。
いくつかの条件が重なるとSQLインジェクションなど脆弱性を引き起こします。

プリペアドステートメントとプレースホルダによるバインディングを駆使して、
文字列内の記号が悪さを引き起こさないよう、
言語が備える仕組みを最大限利用するべきです。

php
1// $departure = $_POST['departure']; かなにかだとして
2if (isset($departure)) {  // !empty() でもいいかもしれん
3  $stmt = $pdo->prepare('SELECT id FROM plan WHERE departure = :departure');
4  $stmt->bindValue(':departure', $departure, PDO::PARAM_STR);
5  $stmt->execute();
6}
7else {
8  $stmt = $pdo->prepare('SELECT id FROM plan');
9  $stmt->execute();
10}

みたいにしてみてはいかがかと。

投稿2020/01/04 07:30

編集2020/01/04 07:49

退会済みユーザー

総合スコア0

mmmn

2020/01/04 09:23

回答ありがとうございます。試してみたのですが、私が自分のコードのPDOについて理解しきれていなく、「Invalid parameter number」というエラーが出てしまいました。今回は、 $sql = 'SELECT id FROM plan'; if(!empty($departure)) $sql .= ' WHERE departure = '."'" ."$departure" ."'"; という書き方で表示したかったものが表示できました。ご指摘いただいた内容でもうまくできるように、データベース接続とバインド変数について、基本的な理解を深めていきたいと思います。ありがとうございました。

退会済みユーザー

2020/01/04 10:05

PDO接続する際のエッセンスが濃縮されている記事が https://qiita.com/mpyw/items/b00b72c5c95aac573b71 にあるので機会を作ってでも読んでいただきたく。濃縮具合が濃いので、一読して理解できない場合は前提となる情報や経験が足りないことにもなります。データベース操作は、この記事を理解して自分の技術として活用できるようになるところを目指すとよいかと思います。

退会済みユーザー

2020/01/04 10:06

悪いことは言わないから、プリペアドステートメントとプレースホルダによるバインディングを駆使できるようになった方がいいよ。それを使いこなせるだけで脆弱性をひとつ排除できるのだし。

mmmn

2020/01/05 08:30

記事読みました。プレースホルダを使って実行できるようになりました！！「変数展開を使ってSQLを組み立てる」ことが危険だということも、記事を読んでようやく理解できました。ご丁寧に教えてくださり本当にありがとうございました。

mmmn

2020/01/05 08:41

躓いていた箇所は、 $stmt->bindValue(':departure', $departure, PDO::PARAM_STR); を使わずに、 $stmt = $pdo->prepare('SELECT id FROM plan WHERE departure = :departure'); $data = array(':departure' => $departure); $stmt->execute($data); という書き方になる関数を真似て使用していたためです。記事の中で３ステップで実行、２ステップで実行とどちらのやり方も紹介されていたので大変参考になりました。ありがとうございました。

行動規範の内容に同意します

ベストアンサー

まず大前提としてPHPなどのプログラムから実行させる前に直接DBに対して実行して想定の結果が得られるSQLを作ってください。

もし、それがSELECT id FROM plan WHERE departure = "大阪府"なのであれば、あとはPHPの文字列の作り方の問題です。
引用符（シングルクォーテーション）と二重引用符（ダブルクォーテーション）の扱いの問題です。
SQLの問題ではありません。

代表的なルール

シングルクォーテーション内では変数は展開されない
ダブルクォーテーション内では変数が展開されるが前後の文字列での注意が必要
シングルクォーテーションの中にダブルクォーテーションはそのまま書けてそのまま表示できる
ダブルクォーテーションの中にシングルクォーテーションはそのまま書けてそのまま表示できる
シングルクォーテーション内でシングルクォーテーションを書いて表示させたい場合はエスケープを行う
ダブルクォーテーション内でダブルクォーテーションを書いて表示させたい場合はエスケープを行う

ざっとこんな感じです。
（細かいニュアンスとかルールはともかく）

PHPマニュアル：文字列にも書いてあります。
まずはそこをおさえた上で対応しないと今後もっと基本的なところでつまずくことになります。

できれば、そこをきちんとおさえた上で、m6uさんの回答にあるSQLインジェクション対策含めたコーディングを行ってもらいたいです。

補足：
MySQLにおいて、文字列を囲うときはシングルクォーテーション、ダブルクォーテーションどちらでも良い仕様なのですが、多くのDBではシングルクォーテーションしか使えないのが仕様です。
プログラミングにおいてずっと続けていたらMySQLだけを使うことというのは中々ないので（むしろMySQLを取り扱っていることが少ない）、シングルクォーテーションで統一しておきましょう。

投稿2020/01/04 08:34

編集2020/01/04 08:45

m.ts10806

総合スコア80850

退会済みユーザー

2020/01/04 08:40

SQL文では、文字列リテラルはシングルクォートかなと。重箱の隅をつつくようでｽﾏﾝ

m.ts10806

2020/01/04 08:42

MySQLではどっちも大丈夫と言うのがあるのでとりあえずスルーしました。シングルで覚えてもらった方が別のDBやるときに混乱しなくて済むんですけどね。一応追記しときます。

退会済みユーザー

2020/01/04 08:43

いいのかMySQL、かしこまりました

m.ts10806

2020/01/04 08:46

私も割と最近知りました。 https://dev.mysql.com/doc/refman/5.6/ja/string-literals.html ダブルクォーテーションで覚えてしまうと他のDBで絶対苦労しますね。 PHPから実行するときに直に書くことはほぼないでしょうけど。

mmmn

2020/01/04 09:18 編集

回答ありがとうございます。 $sql = 'SELECT id FROM plan'; if(!empty($departure)) $sql .= ' WHERE departure = '."'" ."$departure" ."'"; 上記の書き方で表示することができました。根本的な解決ではなさそうですが、ひとまず動きました。引用符と二重引用符の扱いについてマニュアル拝見しました。 SQLはシングルクォーテーションで統一するようにします。ありがとうございます。

m.ts10806

2020/01/04 09:59

無駄なクォートが多いです。下記で十分。 departure =\'' .$departure .'\''; ただ、プリペアドステートメント使わないのでしたら別の形でSQLのエスケープをしてください。いずれにしても、PHPマニュアルを確認すること、エラーは読むことです。

mmmn

2020/01/05 07:35

ありがとうございます。「'」を「\'」として記述することまでは分かったのですが、それをさらに「'\''」シングルクォーテーションで囲うところが抜けていたので、記述いただいたコードを見てすっきりしました。エスケープについて全然分かっていなかったので勉強になりました。別の形でSQLのエスケープというのは、 departure =\'' .$departure .'\''; 上記のコード以外の箇所のことでしょうか？

行動規範の内容に同意します

あなたの回答