inserting 2d array into mysql

Question

I'm trying to insert values of $array into MySQL and I'm facing a problem that the values I'm getting in the database are zeros:

$v = mysqli_real_escape_string($connect, $array[0][0]);

mysqli_query($connect, 'INSERT INTO myTable(col)VALUES("'.$V.'")');

You should learn to use prepared statements instead of substituting variables. Even using mysqli_real_escape_string() is not as safe. — Barmar
– Barmar, Commented Aug 29, 2018 at 23:33
@Barmar especially if you end up using a different variable than the one you escaped ;) — Don't Panic
– Don't Panic, Commented Aug 30, 2018 at 0:07
@Don'tPanic He could still get the variables wrong. $v = $array[0][0]; mysqli_stmt_bind_param("s", $V); — Barmar
– Barmar, Commented Aug 30, 2018 at 0:17
@Barmar sure, definitely. I just meant that you could accidentally concat a variable that that you hadn't escaped, which could potentially cause worse problems than just binding the wrong value. Granted, the chances of there just happening to be a $V containing some SQL injection seem pretty low, but who knows. — Don't Panic
– Don't Panic, Commented Aug 30, 2018 at 0:20

Don't Panic · Accepted Answer · 2018-08-30 03:41:21Z

2

You've set the value to the variable $v, but you're using capital $V in your SQL when you execute the query. PHP variables are case sensitive, so $V is undefined. It's just a typo, but I'll try to explain how it ends up as a zero in your database.

The undefined variable evaluates to null, but you're using it in a string context, so it becomes an empty string. That empty string gets converted to zero by MySQL when you try to insert it into a numeric type column.

You can catch problems like this if you enable warnings and notices on your development server. PHP will tell you about undefined variables, which will make it easier to avoid problems like this.

edited Aug 30, 2018 at 3:41

answered Aug 29, 2018 at 23:24

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

2 Comments

Funk Forty Niner Over a year ago

Hey man, real good answer. I was a bit eesh about your saying it was "just a typo" (lol), but then continuing to "explain" why it becomes empty etc. Got my vote.

Don't Panic Over a year ago

Thanks @Funk. :) I originally CV'd as typo, but then the comment I was leaving to try to explain why the typo had that effect was getting a little out of hand, so I figured I should just make it an answer.

Collectives™ on Stack Overflow

inserting 2d array into mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related