在PHP中插入时MySqli错误，但相同的查询字符串在终端中执行正常

Question

I have some form data that need to be written into a database, after it's confirmed. I form a query string and pass it to mysqli, but get error that my primary key can't be null. It's not null, and query string is executed fine in mysql terminal.

$query_string = "insert into podnosilac (ime, prezime, jmbg) 
        values (
        aes_encrypt('". $mysqli->real_escape_string($_SESSION["ime"])."', @key_str, @init_vector), 
        aes_encrypt('". $mysqli->real_escape_string($_SESSION["prezime"])."', @key_str, @init_vector),
        aes_encrypt('". $mysqli->real_escape_string($_SESSION["jmbg"])."', @key_str, @init_vector)
)";
if (!$mysqli->query($query_string)) {
        echo $mysqli->error;
}

This is the value of $query_string when I echo it just above mysqli->query line:

insert into podnosilac (ime, prezime, jmbg)
    values ( aes_encrypt('ivana', @key_str, @init_vector), aes_encrypt('corovic', @key_str, @init_vector), aes_encrypt('12345678910', @key_str, @init_vector) )

As a matter of fact, when I try this line:

$mysqli->query("insert into podnosilac (ime, prezime, jmbg)
    values ( aes_encrypt('ivana', @key_str, @init_vector), aes_encrypt('corovic', @key_str, @init_vector), aes_encrypt('12345678910', @key_str, @init_vector) )");

I still get "Column 'jmbg' cannot be null".

It's something to do with aes_encrypt. @key_str and @init_vector are defined on database, maybe this is not the way to pass them?

Why could this be happening?

Answer 1

I can think two things:

1) You are not connecting to the correct database

2) You are not setting these mysql variables when you create the connection to database. Do something like $mysqli->query('SET @yourvariable := whatever'); right after your mysqli_connect because these variables are session-specific. This could explaing the query working on your terminal (where you actually setting these variables) and not working in your script (where you don't).