⬆️ ⬇️

A minor feature of CHAR and VARCHAR

Prehistory





There is a small server on which standard LAMP is spinning. It all started with the fact that QA approaches me and says: “There is a topic, I need to double-check the registration of users, can you delete the old account?”, “No question” - I answered. The point is, the entrance is made only through social networks. In order not to violate the integrity of the database by deleting the account, I decided to just take and rename the UID (user ID in a specific social network) in the table.

Since everyone's UID is different (vk, facebook, google ... - numeric UID, linkedin - string UID) was used by VARCHAR for storage. As a result, I added the underscore character `_` to the line, and with a calm soul unsubscribed:" Check ... ".



image

')





I was very surprised when I heard: “Did you just delete the account, otherwise my old one is displayed?”.

In the course of the mini investigation, a discrepancy was found.



/** * @param string $providerUserId * @param string|null $provider * * @return ent\UserSocial|null */ public function getByProviderUserId($providerUserId, $provider = null) { $where = 'providerUserId = ?'; if ($provider) { $where .= ' AND provider = "' . $provider . '"'; } $res = $this->fetchObjects($where, [$providerUserId]); if (empty($res)) { return null; } return $res[0]; } 




Namely:

$ where = 'providerUserId =?';




It turned out the addition of `_` - had no effect on the sample, since the UID was a number.

During the experiments, the following data were obtained:



Initial data
-- --------------------------------------------------------

-- Host: localhost

-- Server version: 5.5.49-0+deb8u1 - (Debian)

-- Server OS: debian-linux-gnu

-- HeidiSQL Version: 8.3.0.4694

-- --------------------------------------------------------



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;



-- Dumping database structure for test

CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;



-- Dumping structure for table test.t

CREATE TABLE IF NOT EXISTS `t` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`string` varchar(50) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;



-- Dumping data for table test.t: ~5 rows (approximately)

/*!40000 ALTER TABLE `t` DISABLE KEYS */;

INSERT INTO `t` (`id`, `string`) VALUES

(1, '123456'),

(2, '123456_'),

(3, '123456a'),

(4, '1234567'),

(5, '123456_a');

/*!40000 ALTER TABLE `t` ENABLE KEYS */;



-- Dumping structure for table test.t2

CREATE TABLE IF NOT EXISTS `t2` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`string` char(50) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `string` (`string`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;



-- Dumping data for table test.t2: ~5 rows (approximately)

/*!40000 ALTER TABLE `t2` DISABLE KEYS */;

INSERT INTO `t2` (`id`, `string`) VALUES

(1, '123456'),

(2, '1234567'),

(3, '123456a'),

(4, '123456_'),

(5, '123456_a');

/*!40000 ALTER TABLE `t2` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;

/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;









Test number 1:



mysql> select * from t where `string` = 123456;

+----+----------+

| id | string |

+----+----------+

| 1 | 123456 |

| 2 | 123456_ |

| 3 | 123456a |

| 5 | 123456_a |

+----+----------+

4 rows in set, 2 warnings (0.00 sec)





Test number 2:



mysql> select * from t where `string` = '123456';

+----+--------+

| id | string |

+----+--------+

| 1 | 123456 |

+----+--------+

1 row in set (0.00 sec)





You need to check the usual CHAR (well, add the index, you never know ...)

Test number 3:



mysql> select * from t2 where `string` = 123456;

+----+----------+

| id | string |

+----+----------+

| 1 | 123456 |

| 3 | 123456a |

| 4 | 123456_ |

| 5 | 123456_a |

+----+----------+

4 rows in set, 3 warnings (0.00 sec)





Test number 4:



mysql> select * from t2 where `string` = '123456';

+----+--------+

| id | string |

+----+--------+

| 1 | 123456 |

+----+--------+

1 row in set (0.00 sec)





Going to the official on the page , I did not find anything similar. As a result, I had to tweak the request so that the UID would be perceived as a string, and not as a number.



Ps. Now when I need to delete something, I add `_` ahead :)

PPS: Link by ellrion to the description of this chip.

Source: https://habr.com/ru/post/302804/



All Articles