Number goes wrong in to database in PHP

Question

I have a situation where I have to add a phone number to the database. If I enter number something like: 868150705 It goes OK to database If I enter something like this: 3706150705 It goes to database with value 2147483647

With this input I take the value out of form

<input type="text" class="demoInputBox" maxlength="20" name="telefonas" value="<?php if(isset($info['tel_nr'])) echo $info['tel_nr']; ?>">

And with this query I put it into database (I have the $username)

$telnr = $_POST['telefonas'];
$db_handle = new mysqli("localhost", "root", "xxx", "Database");
$query = "UPDATE table SET tel_nr = '$telnr' WHERE username = '$username'";
$result = $db_handle->query($query);

My field tel_nr has the below format:

tel_nr int(20)

Can you help me with this strange magic? Btw I know this code is unsafe but the project isn't live at the moment. Just test things.

1. Use prepared statements. 2. Don't quote numbers. 3. Don't use an `int` datatype for a phone number. 4. The code not being live and "just for testing" is not a valid excuse to relax into the bad habit of injecting variables into SQL. 5. See nos. 1 and 4. — Siyual, Jun 07 '17 at 19:56
check this for int limit https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes — Ravinder Reddy, Jun 07 '17 at 19:56

score 4 · Accepted Answer · answered Jun 07 '17 at 20:00

You're trying to put integer greater than int limit to the database. I'd suggest using VARCHAR for this (phone number isn't integer anyway - consider something like +420 730 500 600). Also, you are not escaping the data you get before trying to put it in the database, so it is vulnerable to SQL injection.

Hope this helps you, comment if you have any questions

glenn ferns · Answer 2 · 2017-06-07T20:13:20.353

1

I would recommend switching to a BIGINT for the tel_nr since it is 64bit by default and int is 32 bit (2147483647=2^31-1). the 20 in INT(20) specifies the number of characters mysql displays so in your case with zero fill on it would display 2147483647 preceded by 10 zeros

UPDATE: found where i had read it https://stackoverflow.com/a/4769436/6054257

edited Jun 07 '17 at 20:13

answered Jun 07 '17 at 20:01

glenn ferns

117
6

You are correct that `int` is 32-bit and `bigint` is 64-bit, but that has absolutely nothing to do with the system being 32- or 64-bit. – Siyual Jun 07 '17 at 20:02
my mistake. I should've verified the 32 bit system comment before posting. – glenn ferns Jun 07 '17 at 20:06

Number goes wrong in to database in PHP

2 Answers2