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

Question

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.

I don't know php but looks like its related to http://stackoverflow.com/questions/15532635/converting-a-sting-to-an-integer-returns-2147483647 — ASM, May 28 '16 at 04:25
you dont need to assign unique id . use id field as primary key and auto increment true. — shubham715, May 28 '16 at 04:38
I did not consider the limits of the int variables in a 32 bit system. I appreciate the tip to avoid the problem. — Maurizio Tazzer, May 28 '16 at 23:19

Julie Pelletier · Answer 1 · 2016-05-28T23:31:45.597

3

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.

edited May 28 '16 at 23:31

answered May 28 '16 at 04:34

Julie Pelletier

1,740
1
10
18

the code is indeed vulnerable to sql injection, though php / mysql doesn't support stacked queries. – Loïc May 28 '16 at 04:38
actually there is a "new" native function http://php.net/manual/en/mysqli.quickstart.multiple-statement.php nevermind my comment. – Loïc May 28 '16 at 04:42
I am not sure how "new" it is. Maybe a decade old. – Drew May 28 '16 at 05:42
I see your points and thank you for the corrections. The idea of using random numbers comes from an irrational fear of having two kids clicking at the same time and one getting the id and the other getting an error which would be harder with large random numbers. At least that was the idea of the programmer who gave the idea to me. And the quotation marks inside the sql are there to concatenate the string with the php variables. I believe they are not being passed into MySQL. Please correct me if I'm wrong. And I will check the vulnerability issue, – Maurizio Tazzer May 28 '16 at 23:15
Auto-incremented fields never produce duplicates. – Julie Pelletier May 28 '16 at 23:30
Oops for the quotes. You're absolutely right, so I updated my answer. – Julie Pelletier May 28 '16 at 23:33

score 0 · Accepted Answer · answered May 28 '16 at 23:56

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.

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

2 Answers2