2147483647 getting inserted into DB field even though the datatype is VARCHAR

Question

This is something that we have been banging our heads over for almost a week now.

What's happening?

In our application we have a field for telephone number, which relates to a database field with data-type VARCHAR(25). WE ARE NOT USING INT,BIGINT.
On form submission we are storing the data as following.
1. $_POST
2. sanitize data using filter_input(INPUT_POST, 'mobile', FILTER_SANITIZE_STRING);
3. Using mysqli->prepare to insert the data into database, where the bind-params are also properly set. This field is set to map with "s" (string).
4. Data is then stored into the DB.
Now we are logging all these values in a separate log files as we were debugging the issue. The most strange this has been discovered while looking at log files. It seems that the data correctly came when sent via POST, was still good after sanitation, was still good when it was retrieved immediately after insertion into the DB. But when the same value was fetched by our frontend application to be displayed to the user-end, the data fetched was 2147483647.
This issue doesn't happen with all such insertion, it happens very randomly, and rarely, but has brought us to a point, where I am pulling my hair-out to find a solution.
Development Platform information: PHP 5.3, MySQL 5.5.31

Any help would be greatly appreciated.

Code

Here's the code:

$Mobile = filter_input(INPUT_POST, 'mobile', FILTER_SANITIZE_STRING);

$stmt = $mysqli->prepare("INSERT INTO enquiry(idAdminUsers, isConverted, Firstname, Lastname , Residence , WorkCollege , Gender , Age , Occupation , Mobile , Email , Transport , FirstMile , LastMile , Commute , NoPublicTransport , Member , YesMember , PurposeMember , NoMember , Note, Date, FirstMileCost, FirstMileTime, LastMileCost, LastMileTime, landlineNumber) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?,?,?,?)");
    if ($stmt) {
        if ($stmt->bind_param('iisssssissssssssisssssssss', $idAdminUsers, $isConverted, $firstName, $lastName, $Residence, $WorkCollege, $Gender, $Age, $Occupation, $Mobile, $Email, $Transport, $FirstMile, $LastMile, $Commute, $NoPublicTransport, $Member, $YesMember, $PurposeMember, $NoMember, $Note, $FirstMileCost, $FirstMileTime, $LastMileCost, $LastMileTime, $landlineNumber)) {
$stmt->execute()'

as soon as it is inserted the retrieval happens by following

$stmt = $mysqli->prepare("SELECT idEnquiry , idAdminUsers , isConverted , Firstname , Lastname , Residence , WorkCollege , Gender , Age , Occupation , Mobile , Email , Transport , FirstMile , LastMile , Commute , NoPublicTransport , Member , YesMember , PurposeMember , NoMember , Note, Date, FirstMileCost, FirstMileTime, LastMileCost, LastMileTime, landlineNumber FROM enquiry WHERE idEnquiry = ?");
    if ($stmt) {
        if ($stmt->bind_param('i', $idEnquirySelect)) {
            if ($stmt->execute()) {

this is getting logged, and shows that the data was correctly entered. But as soon as we go and check in the database very randomly data is incorrect.

Following is the Database table create script:

CREATE TABLE IF NOT EXISTS `enquiry` (
  `idEnquiry` int(11) NOT NULL AUTO_INCREMENT,
  `Firstname` varchar(255) DEFAULT NULL,
  `Mobile` varchar(25) DEFAULT NULL,
  `Email` varchar(45) DEFAULT NULL,
  `Age` int(16) DEFAULT NULL,
  `idAdminUsers` int(11) DEFAULT NULL,
  `Member` tinyint(4) DEFAULT NULL,
  `isConverted` tinyint(4) DEFAULT NULL,
  `Note` text,
  `Residence` varchar(255) DEFAULT NULL,
  `WorkCollege` varchar(255) DEFAULT NULL,
  `Transport` varchar(31) DEFAULT NULL,
  `FirstMile` varchar(31) DEFAULT NULL,
  `LastMIle` varchar(31) DEFAULT NULL,
  `Gender` varchar(8) DEFAULT NULL,
  `Commute` text,
  `NoPublicTransport` varchar(64) DEFAULT NULL,
  `YesMember` varchar(255) DEFAULT NULL,
  `PurposeMember` varchar(128) DEFAULT NULL,
  `NoMember` varchar(128) DEFAULT NULL,
  `Occupation` varchar(64) DEFAULT NULL,
  `Lastname` varchar(255) DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `FirstMileCost` varchar(45) DEFAULT NULL,
  `FirstMileTime` varchar(45) DEFAULT NULL,
  `LastMileCost` varchar(45) DEFAULT NULL,
  `LastMileTime` varchar(45) DEFAULT NULL,
  `landlineNumber` varchar(15) NOT NULL,
  PRIMARY KEY (`idEnquiry`),
  UNIQUE KEY `idenquiry_UNIQUE` (`idEnquiry`),
  UNIQUE KEY `Email_UNIQUE` (`Email`),
  KEY `idAdminUserFK_idx` (`idAdminUsers`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='All the enquiry details are added to this table.' AUTO_INCREMENT=482 ;

Does the correct data show within your database? Can you show us the code of the frontend application in question? — ashin999, May 15 '14 at 10:51
Can you confirm that the value stored in database is indeed 2147483647 (query the database directly, not through your front-end app?) — Mchl, May 15 '14 at 10:52
My guess is its saved correctly in the DB, but the front end app that retrieves it is both running on a 32bit system, and casts the string to an int. — Steve, May 15 '14 at 11:02
@ashin999 : No. Even the database has wrong data. But when inserted, and immediately retrieved by our Logging function. It displays correct data. — parthk, May 15 '14 at 11:09
@nl-x : I have put the code. Also attaching the DB table structure. — parthk, May 15 '14 at 11:15
@parthk I see in your code how you do the retrieval of the inserted data. But you don't show us how you echo out the value that you say looks correct. Please show that code. Also, if you say it looks correct, then it means the data is correctly in the database... And also please show us the code that shows you the wrong data... How do you get it and how do you echo it ? ... We need to rule out if you are echoing it wrong, or if the correct data is later overwritten by another query. — nl-x, May 15 '14 at 11:28
@nl-x : Data is not being displayed correctly. And this doesn't happen with all the insertions, it happens randomly. The echoed data comes wrong only. But for some strange reason the log entry, that retrives the data to log it immediately after insertion displays correct data. — parthk, May 15 '14 at 11:30
Do you have any triggers which update the table? Any other scripts running to update the table? — Kickstart, May 15 '14 at 11:38
@kickstart Nope. No trigger whatsoever. But we plan to put one in place tonight to note how/when this change occurs. — parthk, May 15 '14 at 11:47
@KraneBird : this specific pages are only created to insert data, and this data doesn't get changed at anywhere in the process. No place to edit/delete this inserted data. — parthk, May 15 '14 at 11:47
@KraneBird : that was the first thing that we did. This application is currently in production, so we pushed it without the sanitization and waited for one day to receive more real enquiries, and it still happened with one of them too :( — parthk, May 15 '14 at 12:14
Duplicate: http://stackoverflow.com/questions/15211137/varchar-issue-on-mysql-2147483647?lq=1 — SSpoke, May 15 '14 at 12:48
@SSpoke Could you be more specific about the screenshot? I can post all the information that is required. — parthk, May 15 '14 at 13:13
@parthk - Just to clarify. you wrote "this is getting logged, and shows that the data was correctly entered. But as soon as we go and check in the database very randomly data is incorrect". Does that mean that every time the select query is running *for the first time after the insert was made* and being logged - it shows (in the log) that the data entered was correct and then when you check the db - sometimes it's correct and sometimes 2147483647? — Nikita 웃, May 15 '14 at 18:13

Nikita 웃 · Accepted Answer · 2018-08-10T16:30:51.130

Analysis

You got 2 major clues here.

First Clue: 2147483647

What do we know about 2147483647?

From Wikipedia (http://en.wikipedia.org/wiki/2147483647):

The number 2,147,483,647 is also the maximum value for a 32-bit signed integer in computing. It is therefore the maximum value for variables declared as int in many programming languages running on popular computers.

How can we get this number created by the database?

In several ways, all related to INT, but since your "Mobile" field data type is VARCHAR and not INT, as you clearly emphasized, we know that the problem is not in the database itself. If the field was INT, we could have continued exploring the database itself for possible issues. So we eliminate this possibility. This leaves us with possible code issues.

Second Clue: The log

this is getting logged, and shows that the data was correctly entered. But as soon as we go and check in the database very randomly data is incorrect.

Every time the select query is running for the first time after the insert was made and being logged - it shows (in the log) that the data entered was correct and then when you check the db - sometimes it's correct and sometimes it's 2147483647.

The (usual) Suspect

Based on this information, it would be safe to assume that PHP is converting the string to integer somewhere and then put/update this value in the db, after it has already been correctly inserted.

Debugging

There are several good ways to debug this:

1) Search if there is another UPDATE query that enters data to the Mobile field and overwrites the correct data that was inserted. A full code search of all your files for the term "Mobile" can be a good start.

2) Search for any occurrence of intval in your code, as this function (or other similar functions/methods) can convert a string to 2147483647: Converting a String to an Integer returns 2147483647

3) Search for any other occurrences of 2147483647 in your MySQL database, just to determine if this is an isolated problem or if anything else get this value and might have this value copied to the Mobile field via some query or another method.

Hope this helps.

Thank you Mikey, this did help. The was an update query running to mark the Enquiry getting converted into actual registration, and within that, the prepare statement was mapped by an 'i' (integer) instead of 's'. By doing a full search this was revealed. Thanks a bunch again! — parthk, May 16 '14 at 04:37

score 1 · Answer 2 · answered May 15 '14 at 21:19

...was still good when it was retrieved immediately after insertion into the DB. But when the same value was fetched by our frontend application to be displayed to the user-end, the data fetched was 2147483647.

Could it be that the data displayed is 2147483647 and not the data fetched?

Because if it is the latter, then this means that something is changing the data in your database and you need to check the backend and/or the property modification forms, if any.

If the former, as I'm led to believe, then it could be that a very specific and rare format of mobile number (maybe containing spaces? The + prefix? A dash? Other?) is mistakenly passed, maybe partially (e.g. the un-prefixed part) to intval() or similar function, to be formatted for display.

Verify whether one of the "wrong" numbers is still intact in the DB; if it is, just follow the controller and view path whereby the number gets displayed as 2147483647.

2147483647 getting inserted into DB field even though the datatype is VARCHAR

2 Answers2

Analysis

Debugging