CODEWITHSUNDEEP
phpmysql
Maurizio Tazzer
Maurizio Tazzer

Reputation: 11

MySQL insert is writing a different value than the one I am entering

I just setup a LAMP server in an Amazon Linux instance. Everything is working fine and all my connections with my database are working great.

But I'm having a problem that I just can't figure out. I am working on an exam system and I want to give a random id to each student that is going to take it. I don't need any info about the students so I don't have any relevant fields that I could use as unique identifiers.

$id_escuela=2;
$id_grupo=$_POST['grado'];
$id_eval=rand(1,9999999999);
$sql = "INSERT INTO pinion_evalua (id_eval,id_escuela,anio) VALUES (".$id_eval.",".$id_escuela.",".$id_grupo.")";

On my local server everything works great and I get random numbers every time, but when I upload my files to the Amazon Server, I'm stuck with a specific number (2147483647), even if the result of the rand is different. For instance, I get:

Error: INSERT INTO pinion_evalua(id_eval,id_escuela,anio) VALUES (4612160288,1,2)
Duplicate entry '2147483647' for key 'id_eval'

How can this even be possible? I tried removing the random part and use an autoincrement but I still receive the same number. I even tried reducing the size of the int but I still get number, even if it goes against the rules of the field. It's driving me mad.

Do any of you can give me a hint about what can be wrong?

Regards.

Upvotes: 0

Views: 262

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179084

The maximum value for an INT column in MySQL is exactly 2147483647.

http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Any time you use a larger value, it's getting truncated down to its maximum. The behavior you are observing is correct, and expected.

You will have the same problem with an AUTO_INCREMENT when the largest value in the column is also the maximum value for that column. The server will try to increment by 1, the value will be truncated to the maximum possible value, and the insert will fail.

Upvotes: 0

Julie Pelletier
Julie Pelletier

Reputation: 1716

You have many problems in those few lines of code:

  • You are obviously running PHP on a 32 bit system and try to get a random value bigger than that.
  • Since you don't care about the id assigned, why not use an auto-incremented number in MySQL?
  • Your code is susceptible to SQL injection which can be easily demonstrated by inputting 3); delete pinion_evalua where (1 = 1. The proper solution would be to use prepared statements as documented in the PHP manual.

Upvotes: 3

Related Questions