SQLでINSERT文の中にサブクエリーがある場合、どのようにしてPrepared Statmentを使えばいいのかを知りたい。

Question

前提

PHPとMySQLを使ってログインのシステムを作っています。
SQLインジェクションを防ぐためにPrepared Statementを使っているのですが、

INSERT INTO user(email,first_name,last_name,city,state,postcode,address_id)Values(?,?,?,?,?,?,(SELECT id FROM address WHERE address1 = ?));

本当はこの”?”に$address1という$_POSTで取得した変数を入れたい。
*addressのテーブルにaddress1というカラムがあります。

のようにすると何もデータベースにデータが入りません。色々調べた結果、この方法ではできないことがわかったのですが、Foreign Keyとしているaddress_idを取得するためには上記のようにSELECT文を使ってidを取得する方法くらいしか今の所思いつかなく、詰まっています。

実現したいこと

ここに実現したいことを箇条書きで書いてください。

• Prepared Statementを使って外部キーのIDを他のテーブルから取得してきてデータをデータベースにINSERTしたい。

発生している問題・エラーメッセージ

特にエラーメッセージは出ていません。Formをサブミットすると、ページがリロードして終わりといった感じです。データはデータベースに送信されていません。

該当のソースコード

<?php
    include "config.php";

    if($_SERVER["REQUEST_METHOD"] == "POST"){
        include "isRegistered.php";
        $email = mysqli_real_escape_string($conn, $_POST["email"]);
        $firstName = mysqli_real_escape_string($conn,$_POST["firstName"]);
        $lastName = mysqli_real_escape_string($conn,$_POST["lastName"]);
        $password = mysqli_real_escape_string($conn,$_POST["password"]);
        $confirm_password = mysqli_real_escape_string($conn,$_POST["confirmPassword"]);
        $country = mysqli_real_escape_string($conn,$_POST["country"]);
        $address1 = mysqli_real_escape_string($conn,$_POST["address1"]);
        $address2 = mysqli_real_escape_string($conn,$_POST["address2"]);
        $city = mysqli_real_escape_string($conn,$_POST["city"]);
        $state = mysqli_real_escape_string($conn,$_POST["state"]);
        $postCode = mysqli_real_escape_string($conn,$_POST["postcode"]);
        
        if(!isRegistered($email)){
            if($password == $confirm_password){
                $sql = "INSERT INTO address(address1,address2,city,state,postcode,country_id)
                VALUES(?,?,?,?,?,(SELECT id FROM country WHERE country_name = ?))";
                $stmt = mysqli_stmt_init($conn);
                if(!mysqli_stmt_prepare($stmt,$sql)){
                    echo "SQL error : INSERT INTO address failed";
                    echo $conn -> error;
                }else{
                    mysqli_stmt_bind_param($stmt,"sssssi",$address1, $address2,$city,$state,$postCode,$country);
                    mysqli_stmt_execute($stmt);
                  
                }

                $sql2 = "INSERT INTO user(first_name,last_name,email,password,address_id)VALUES(?,?,?,?,(SELECT id FROM address WHERE address1 = ?))";
                $stmt2 = mysqli_stmt_init($conn);
                if(!mysqli_stmt_prepare($stmt2,$sql2)){
                    echo "SQL error : INSERT INTO user failed";
                    echo $conn -> error;
                }else{
                    $encrypted_password = password_hash($password,PASSWORD_BCRYPT);
                    mysqli_stmt_bind_param($stmt2,"ssssi",$firstName,$lastName,$email,$encrypted_password,$address1);
                    mysqli_stmt_execute($stmt2);
                    echo "<p style='color:green;text-align:center;margin-top:1rem;
                    font-weight:bold;'> User account successfully created! <p>";
                }
        
             }else{
                    echo "<p style='color:red;text-align:center;margin-top:1rem;
                    font-weight:bold;'>Please Re-confirm your password <p>";
             }  

        }else{
            echo "<p style='color:red;text-align:center;margin-top:1rem;
            font-weight:bold;'>Email already in use. Try again! <p>";
        }
    }

試したこと

直接

INSERT INTO user(email,first_name,last_name,city,state,postcode,address_id)Values(?,?,?,?,?,?,(SELECT id FROM address WHERE address1 = ?);

のようにサブクエリーを記述するのはできないと思ったので、そのサブクエリーを分けて実装した感じになります。そのため、セレクト文を使うたびにprepared statementを使わなければいけないと思ったので、コードの量がとてつもなくなってしまいました。Prepared Statementを使うのも初めてなので、使い方が合ってるかはわかりませんが、多めにみてください。：＜

Comments

[maisumakun]

> この方法ではできないことがわかったのですが

・できないとわかった情報源は何でしょうか？
・PHPを使わず、SQL文を直接送信したらどうなりますか？

[zy1088710]

主にスタックオーバーフローで上げられている解答からそう判断しました。https://stackoverflow.com/questions/47379594/how-to-bind-parameters-for-nested-query-in-prepared-statement
説明は英語です。
Param (mysqli_stmt_bind_param) にはカラムやテーブルなどのオブジェクトを入れることはできないと記されていました。

[zy1088710]

訂正です。ここでいうパラムはmysqli_stmt_bind_paramのことではなく、valuesの中の値のことなのかと思います。間違ってるかもしれませんが、

[maisumakun]

> Param (mysqli_stmt_bind_param) にはカラムやテーブルなどのオブジェクトを入れることはできない

それは、「?を列名と対応付けられない」という意味です。VALUESの中にサブクエリをかける書けないとは別の話題です。

[yuma.inaura]

result や row って取得できてますか？

[zy1088710]

「?を列名と対応付けられない」

その理解が正しいです。ありがとうございます。

result や row どちらも取得できていると思います。

[yuma.inaura]

mysqli_fetch_assoc($result); の $result を定義してる場所がないように見えるけどどこで代入されてるんだろう

[zy1088710]

少しコードが長くなって見づらかったと思うので、変更させていただきました。もう一度確認してもらえたら嬉しいです。

結果としては、特にエラーは出ず、しっかりecho "<p style='color:green;text-align:center;margin-top:1rem;font-weight:bold;'> User account successfully created! <p>"; の部分も画面に出力されます。しかしデータベースにデータが追加されない状態です。

Answer 1

INSERT INTO user(email,first_name,last_name,city,state,postcode,address_id)Values(?,?,?,?,?,?,(SELECT id FROM address WHERE address1 = ?);

VALUES以降のカッコが閉じきれていません。

Comments

[zy1088710]

記述し忘れていました。カッコをしっかり閉じても動きません。

Answer 2

addressにおいてidがユニークであれば普通にできるはずです
まずはprepareを一旦おいておいてプリミティブな値で実行してみることです。

create table address(id int primary key,address1 varchar(30));
insert address values
(1,'東京'),(2,'大阪'),(3,'愛知');
create table user(name varchar(30),address_id int);
insert into user(name,address_id) values
('佐藤',(SELECT id FROM address WHERE address1 = '東京')),
('鈴木',(SELECT id FROM address WHERE address1 = '大阪')),
('田中',(SELECT id FROM address WHERE address1 = '東京')),
('吉田',(SELECT id FROM address WHERE address1 = '福岡'));

調整

外部キー制約のサンプル

create table address(id int primary key,address1 varchar(30));
insert address values
(1,'東京'),(2,'大阪'),(3,'愛知');


create table user(
name varchar(30),
address_id int,
foreign key(address_id) references address(id) on delete set null
);
insert into user(name,address_id) values
('佐藤',(SELECT id FROM address WHERE address1 = '東京')),
('鈴木',(SELECT id FROM address WHERE address1 = '大阪')),
('田中',(SELECT id FROM address WHERE address1 = '東京')),
('吉田',(SELECT id FROM address WHERE address1 = '福岡'));

Comments

[zy1088710]

外部キーには　address_id INTとデータ型を指定し、address table の id INT PRIMARY KEYにつなげている形なのですが、address_idはユニークになっていますよね？

[yambejp]

外部キー制約を追記しました
特に問題なく動くのが確認できますよね？

[zy1088710]

特に問題なく動きます。

[zy1088710]

Prepared Statement を使うと、?を列名と対応付けられないため動作しません。

[yambejp]

プリミティブにできることはprepareでも可能です

追記されたソースにはselectをつかったinsert文が見当たりませんが？
prepare処理なのにエスケープしたりちょっとちぐはぐな感じが否めません。
mysqliではなくpdoにすればもう少し簡便な処理がかけそうな気がします
(mysqliのprepareはちょっと特殊な値の渡し方なので)

[zy1088710]

追加したソースコードは自分なりにSubQueryを使わないで実装しようとした結果です。
特殊な渡し方とはどういうことでしょうか？

[zy1088710]

ソースコードを変更しました。特にエラーは出ず、しっかりecho "<p style='color:green;text-align:center;margin-top:1rem;font-weight:bold;'> User account successfully created! <p>"; の部分も画面に出力されます。しかしデータベースにデータは追加されていません。

[yambejp]

とりあえず"sssssi"は"ssssss"ではないですか？
結局そういうところがmysqliのprepareは特殊で面倒なんですよ

[zy1088710]

最後の"i"はcountry_id を指していて、これはINT型なので"i"にしています。

[zy1088710]

すみません、すべて"ssssss"に変更したら動きました！INT型で指定しているのになんで"s"だとうまくいくのかがわかりません..

[yambejp]

> 最後の"i"はcountry_id を指していて、これはINT型なので"i"にしています。

いや、根本的な考え方が間違えています。
DBのカラム型は関係ありません、渡すデータの型を指定します。
文字列を渡してintであるidを返したいのですよね？
であれば型は"s"です

文字列をintで評価すればselectされるデータは無いのでinsertにデータが流れないのでしょうね

[zy1088710]

勘違いしていました。確かに$countryという文字列を渡すので"s"になりますね。idのことを気にしすぎていたのだと思います。ありがとうございます。助かりました！