前提

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
1<?php
2    include "config.php";
3
4    if($_SERVER["REQUEST_METHOD"] == "POST"){
5        include "isRegistered.php";
6        $email = mysqli_real_escape_string($conn, $_POST["email"]);
7        $firstName = mysqli_real_escape_string($conn,$_POST["firstName"]);
8        $lastName = mysqli_real_escape_string($conn,$_POST["lastName"]);
9        $password = mysqli_real_escape_string($conn,$_POST["password"]);
10        $confirm_password = mysqli_real_escape_string($conn,$_POST["confirmPassword"]);
11        $country = mysqli_real_escape_string($conn,$_POST["country"]);
12        $address1 = mysqli_real_escape_string($conn,$_POST["address1"]);
13        $address2 = mysqli_real_escape_string($conn,$_POST["address2"]);
14        $city = mysqli_real_escape_string($conn,$_POST["city"]);
15        $state = mysqli_real_escape_string($conn,$_POST["state"]);
16        $postCode = mysqli_real_escape_string($conn,$_POST["postcode"]);
17        
18        if(!isRegistered($email)){
19            if($password == $confirm_password){
20                $sql = "INSERT INTO address(address1,address2,city,state,postcode,country_id)
21                VALUES(?,?,?,?,?,(SELECT id FROM country WHERE country_name = ?))";
22                $stmt = mysqli_stmt_init($conn);
23                if(!mysqli_stmt_prepare($stmt,$sql)){
24                    echo "SQL error : INSERT INTO address failed";
25                    echo $conn -> error;
26                }else{
27                    mysqli_stmt_bind_param($stmt,"sssssi",$address1, $address2,$city,$state,$postCode,$country);
28                    mysqli_stmt_execute($stmt);
29                  
30                }
31
32                $sql2 = "INSERT INTO user(first_name,last_name,email,password,address_id)VALUES(?,?,?,?,(SELECT id FROM address WHERE address1 = ?))";
33                $stmt2 = mysqli_stmt_init($conn);
34                if(!mysqli_stmt_prepare($stmt2,$sql2)){
35                    echo "SQL error : INSERT INTO user failed";
36                    echo $conn -> error;
37                }else{
38                    $encrypted_password = password_hash($password,PASSWORD_BCRYPT);
39                    mysqli_stmt_bind_param($stmt2,"ssssi",$firstName,$lastName,$email,$encrypted_password,$address1);
40                    mysqli_stmt_execute($stmt2);
41                    echo "<p style='color:green;text-align:center;margin-top:1rem;
42                    font-weight:bold;'> User account successfully created! <p>";
43                }
44        
45             }else{
46                    echo "<p style='color:red;text-align:center;margin-top:1rem;
47                    font-weight:bold;'>Please Re-confirm your password <p>";
48             }  
49
50        }else{
51            echo "<p style='color:red;text-align:center;margin-top:1rem;
52            font-weight:bold;'>Email already in use. Try again! <p>";
53        }
54    }

試したこと

直接

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

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

maisumakun

2023/01/05 00:03 編集

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

zy1088710

2023/01/05 00:34

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

zy1088710

2023/01/05 00:37

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

maisumakun

2023/01/05 00:44

> Param (mysqli_stmt_bind_param) にはカラムやテーブルなどのオブジェクトを入れることはできないそれは、「?を列名と対応付けられない」という意味です。VALUESの中にサブクエリをかける書けないとは別の話題です。

yuma.inaura

2023/01/05 04:28

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

zy1088710

2023/01/05 09:31

「?を列名と対応付けられない」その理解が正しいです。ありがとうございます。 result や row どちらも取得できていると思います。

yuma.inaura

2023/01/05 09:43

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

zy1088710

2023/01/05 10:21

少しコードが長くなって見づらかったと思うので、変更させていただきました。もう一度確認してもらえたら嬉しいです。結果としては、特にエラーは出ず、しっかりecho "<p style='color:green;text-align:center;margin-top:1rem;font-weight:bold;'> User account successfully created! <p>"; の部分も画面に出力されます。しかしデータベースにデータが追加されない状態です。

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

回答2件

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

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

投稿2023/01/04 23:22

maisumakun

総合スコア146550

zy1088710

2023/01/04 23:58

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

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

ベストアンサー

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

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

調整

外部キー制約のサンプル

SQL
1create table address(id int primary key,address1 varchar(30));
2insert address values
3(1,'東京'),(2,'大阪'),(3,'愛知');
4
5
6create table user(
7name varchar(30),
8address_id int,
9foreign key(address_id) references address(id) on delete set null
10);
11insert into user(name,address_id) values
12('佐藤',(SELECT id FROM address WHERE address1 = '東京')),
13('鈴木',(SELECT id FROM address WHERE address1 = '大阪')),
14('田中',(SELECT id FROM address WHERE address1 = '東京')),
15('吉田',(SELECT id FROM address WHERE address1 = '福岡'));

投稿2023/01/05 00:54

編集2023/01/05 08:38

yambejp

総合スコア117680

zy1088710

2023/01/05 08:23

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

yambejp

2023/01/05 08:39

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

zy1088710

2023/01/05 08:56

特に問題なく動きます。

zy1088710

2023/01/05 08:57

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

yambejp

2023/01/05 09:11 編集

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

zy1088710

2023/01/05 09:53

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

zy1088710

2023/01/05 10:20

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

yambejp

2023/01/05 10:46

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

zy1088710

2023/01/05 11:11

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

zy1088710

2023/01/05 11:17

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

yambejp

2023/01/05 11:20 編集

> 最後の"i"はcountry_id を指していて、これはINT型なので"i"にしています。いや、根本的な考え方が間違えています。 DBのカラム型は関係ありません、渡すデータの型を指定します。文字列を渡してintであるidを返したいのですよね？であれば型は"s"です文字列をintで評価すればselectされるデータは無いのでinsertにデータが流れないのでしょうね

zy1088710

2023/01/05 11:30

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

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

あなたの回答