Предыстория
Есть небольшой сервер, на котором крутится стандартный LAMP. Все началось с того, что подходит ко мне QA и говорит: «Есть тема, мне нужно перепроверить регистрацию пользователей, можешь удалить старый аккаунт?», «Не вопрос» — ответил я. Суть в том, вход у нас сделан только через социалки. Что бы не нарушать целостность базы удалением аккаунта, я решил просто взять и переименовать UID (пользовательский ID в конкретной социальной сети) в таблице.
Так как UID у всех разный (vk, facebook, google… — числовой UID, linkedin — строковый UID) был использован VARCHAR для хранения. В итоге я добавил символ нижнего подчеркивания `_` к строке, и со спокойной душой отписался: «Проверяй...».
Я очень сильно удивился, когда услышал: «А ты точно удалил аккаунт, а то отображается мой старый?».
В ходе мини расследования, было найдено место нестыковки.
/**
* @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];
}
А именно:
$where = 'providerUserId = ?';
Оказалось добавление `_` — никак не повлияло на выборку, так как UID был числом.
В ходе экспериментов были получены следующие данные:
Исходные данные
-- --------------------------------------------------------
-- 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 */;
Тест №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)
Тест №2:
mysql> select * from t where `string` = '123456';
+----+--------+
| id | string |
+----+--------+
| 1 | 123456 |
+----+--------+
1 row in set (0.00 sec)
Нужно проверить обычный CHAR (ну и индекс добавим, мало ли что...)
Тест №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)
Тест №4:
mysql> select * from t2 where `string` = '123456';
+----+--------+
| id | string |
+----+--------+
| 1 | 123456 |
+----+--------+
1 row in set (0.00 sec)
Зайдя к официалам на страничку, я ничего похожего не нашел. В итоге пришлось подправить запрос, что бы UID воспринимался как строка, а не как число.
PS. Теперь когда нужно что-то удалить, я добавляю `_` впереди :)
PPS: Линк by ellrion на описание данной фишки.
Поделиться с друзьями
Комментарии (12)
miksoft
07.06.2016 13:50+2Мне казалось что это широко известная фича MySQL, что он кастит строки в числа от начала строки до первого неподходящего символа.
А вообще есть хорошее неписанное правило — не полагайтесь на неявные преобразования типов. Сравнивайте строки со строками, а числа с числами.mamchyts
07.06.2016 13:53Насчет фичи не соглален, впервые с таким столкнулся, это же не поиск а ля LIKE 'xxxx%' .
А вот со вторым — полностью согласен, хотя в данном примере я положился на PDO, и как видно зря…
KlimovDm
07.06.2016 13:59PDO::PARAM_STR — явное указание для типа char/varchar в таблице вряди ли будет лишним. Для того и придуманы.
dougrinch
07.06.2016 17:21+1А в чем проблема-то? При сравнение строки с числом, ожидаемо, получили типопроблемы.
AlexLeonov
07.06.2016 17:27-4перейменовать
Думал, что после «Андройд» ничего хуже уже не будет… Ошибался!
mdl
Думается, проблему бы решило
$where = «providerUserId = '?'»;
Потому как в аналогичной ситуации STRICT_ALL_TABLES и прочее нас не спасло.
MySQL знатно неявно кастит числа, да, хотя и услужливо пишет, что за выполнение запроса select * from t where string = 123456 было два предупреждения. Которые по-умолчанию все равно нигде не пишет.
bat
вы уверены что символ вопроса внутри строки будет воспринят как плейсхолдер?
mdl
Уже нет.
Как ниже правильно заметили, только явное указание PDO::PARAM_STR в PDO::bindParam решило бы.
А про '?' — глупость сморозил. Просто практически никогда не связывался с prepared statements в клиентских приложениях.
bat
а зря
говорите как будто это бяка какая-то ))
весьма полезная штука
автора за подобный код осуждаю
возможно, что там значение из предопределенного списка констант, и тем не менее
mamchyts
Согласен, не хорошо мешать, в данном случае $provider — это предопределенный список, так что ничего ужасного не будет.
Просто на тот момент кто-то поленился создавать отдельный массив для параметров, а $provider не обязательный.