PHP) mysqli_prepare doesn't work in if statement

Question

I am trying to make an english vocabulary app. Reactjs and php is used in this app and currently I have problem with preventing SQL injections.

https://www.php.net/manual/en/mysqli.prepare.php in this link I found I can do

$conn = mysqli_connect('hostname','username','password','databasename');
if($stmt= mysqli_prepare($conn, SQL QUERY)){}

to make a prepared statement, my code is almost same as the example, but just SQL is different

<?php 
header('Access-Control-Allow-Origin: http://localhost:3000/');
$content = file_get_contents("php://input");
$contentJSON = json_decode($content);
$addEnglish = $contentJSON->english;
$addKorean = $contentJSON->korean;
$addSwedish = $contentJSON->swedish;
$addSynonyms = $contentJSON->synonyms;
$addExample = $contentJSON->example;
if (!preg_match('/[^A-Za-z]+/', $addEnglish)&&!preg_match('/\s/', $addEnglish))
{
include 'db/db_connection.php';
$conn = mysqli_connect('localhost','root','','worddb');

if(connectDB($conn)){
    if($mysqliPrepare=mysqli_prepare($conn,
        "CREATE TABLE IF NOT EXISTS WORDLIST(
            wordIndex INT(10) AUTO_INCREMENT,
            english VARCHAR(20),
            korean VARCHAR(20),
            swedish VARCHAR(20),
            synonyms TEXT,
            example VARCHAR(100),
            UNIQUE KEY wordDBIndex (wordIndex)
        );
        INSERT INTO WORDLIST (english, korean, swedish, synonyms, example)
        VALUES (?,?,?,?,?);"
    )){
        echo "prepared ".$mysqliPrepare;
        mysqli_smt_bind_param($mysqliPrepare,"s",$addEnglish,$addKorean,$addSwedish,$addSynonyms,$addExample);
        mysqli_smt_execute($mysqliPrepare);
        mysqli_stmt_bind_result($mysqliPrepare, $district);
        mysqli_stmt_fetch($mysqliPrepare);
        mysqli_stmt_close($mysqliPrepare);
    }
    print_r("</br>error ".mysqli_stmt_error($mysqliPrepare));
    print_r("</br>error ".mysqli_stmt_error($conn));
    print_r("</br>error ".$mysqliPrepare);
}
}else{
echo "search again";
}

connectDB function is from db_connection.php

<?php 
function connectDB($conn){
    $connectStatus = null;
    if (!$conn) {
        $connectStatus = false;
        die("Connection failed: " . $conn->connect_error);
    }else{
        echo "Connected successfully";
        $connectStatus = true;
    }
    return $connectStatus;
}
?>

I tried SQL query in phpmyadmin and it worked there, but in php script it seems like SQL query or mysqli_prepare function is buggy because inside if statement is just never triggered. I mean I don't see message "prepared" which php should show. The table doesn't want to be created too.

That's why I did some mysqli_stmt_error to see what error do I get, but

I get such errors, so I cannot even get mysql error message.. it seems like

if($mysqliPrepare=mysqli_prepare($conn,
    "CREATE TABLE IF NOT EXISTS WORDLIST(
        wordIndex INT(10) AUTO_INCREMENT,
        english VARCHAR(20),
        korean VARCHAR(20),
        swedish VARCHAR(20),
        synonyms TEXT,
        example VARCHAR(100),
        UNIQUE KEY wordDBIndex (wordIndex)
    );
    INSERT INTO WORDLIST (english, korean, swedish, synonyms, example)
    VALUES (?,?,?,?,?);"
))

problem is inside in this code but I can't see any problem with it because same SQL code works perfectly in phpmyadmin. I even tried '?' instead ? but doesn't work.

you should create table separately because every time you want to run this code, you will get error because you are trying to create the same table every time. I suggest you to create a different configuration file to setup schema initially. Remove the create table line for now and test the insert query only. — Pharsa Thapa, Jul 11 '20 at 17:26
You don't need to create the table every time, you can just set it up already in mysql when you install your application — ADyson, Jul 11 '20 at 17:46
When you looked up the mysqli_prepare() statement at php.net you must have missed statement: "Prepares the SQL query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement." — TimBrownlaw, Jul 11 '20 at 18:33
And also the misspelled mysqli_smt_ lines in your code need fixing. If you do separate the statements, as others have already advised and helping you to learn, it does work. — TimBrownlaw, Jul 11 '20 at 18:36
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Jul 11 '20 at 20:07

score 0 · Answer 1 · answered Jul 11 '20 at 17:23

0

I do not think you can combine two queries in the same statement (create + insert). Try splitting them up.

answered Jul 11 '20 at 17:23

hopsoo

305
1
3

Please have a look here: https://stackoverflow.com/questions/11632902/mysqli-can-it-prepare-multiple-queries-in-one-statement – hopsoo Jul 11 '20 at 17:27
Aha ok thanks... didn't know that mysqli_prepare cannot handle multiple queries.. awful :( – game lover Jul 11 '20 at 17:29

PHP) mysqli_prepare doesn't work in if statement

1 Answers1