MySqli error on insert in PHP, but the same query string executes fine in terminal

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?

@EduardoGalván, key_str and init_vector are defined on database, from mysql terminal. — ivanacorovic
– ivanacorovic, Commented Apr 13, 2016 at 14:51
I have a hunch that the SQL variables @key_str and @init_vector are being set up in the SQL command line environment but not the PHP environment. What do you get if you select @key_str, @init_vector from PHP? (Just NULL or not NULL - you don't have to give anything away). Where and how are they set? — Mike
– Mike, Commented Apr 14, 2016 at 6:51

Sharky · Accepted Answer · 2016-04-14 10:29:51Z

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).

edited Apr 14, 2016 at 10:29

answered Apr 14, 2016 at 6:52

Sharky

6,3253 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ivanacorovic Over a year ago

@Mike, key_str and init_vector were null. Those are user defined variables and they are available in mysql terminal for the session only. I have defined $key_str and $init_vector to use in php.

Collectives™ on Stack Overflow

MySqli error on insert in PHP, but the same query string executes fine in terminal

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related