Read it carefully, we have this query which is inserting values in the table called
users. For the value
member_id we are running a subquery to select from the table admin_users the id of the member. The reason why there are
single quotes with
+, it’s because we are trying to manipulate the query. At this moment this first subquery works correctly but what happends with the second subquery?
The second subquery selects the
pass from the table
settings, the table
settings and the value
pass totally exists and there is only one record, but this second query inside the INSERT INTO is not returning nothing. When the execution of the query INSERT INTO finishs, all the values are stored correctly except notes column which finally inserts 0. I don’t know why but if you delete all the
''+ it works correctly the whole sql statement but in this time we can not delete
''+ because we are altering the query. I need a solution for this issue.
INSERT INTO `users` (`username`,`password`,`number`,`member_id`,`exp_date`,`notes`) VALUES ('balvin','sjeneoeoe','3', ''+(select id from `admin_users` where username = 'TEST')+'', '1644622354', '' + (select pass from `settings`));#;');
Also i have tried modifying the second subquery like this but it didn’t work.
'' + (select pass from `settings` LIMIT 1) '' + (select pass from `settings` GROUP BY pass LIMIT 1) '' + (select pass from `settings` where id = 1 LIMIT 1)
Perhaps the error it’s the datatype of the column value pass in settings or the column notes in users
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) DEFAULT NULL, `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `exp_date` int(11) DEFAULT NULL, `notes` mediumtext COLLATE utf8_unicode_ci NOT NULL, `number` int(11) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `member_id` (`member_id`), KEY `exp_date` (`exp_date`), KEY `username` (`username`), KEY `password` (`password`), ) ENGINE=InnoDB AUTO_INCREMENT=1702894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `settings` ( `id` int(11) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL, `pass` mediumtext COLLATE utf8_unicode_ci NOT NULL, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+ is only for arithmetic.
'' is treated as
0 when used in arithmetic. Ditto for strings that don’t start with a number.
You don’t need
... , ( SELECT ... ), ...
On the other hand, it is usually better to do
INSERT INTO t (a,b,c) SELECT ... AS a, ... AS b, ... AS c FROM ... WHERE ...
SELECT can contain a
JOIN if necessary.
On to your second attempt:
'' + (select pass from `settings` LIMIT 1)
First get rid of the arithmetic,…
(select pass from `settings` LIMIT 1)
Now think about what is in
settings. Are there multiple rows? If so, which one with the “limit 1” give you? You cannot predict. Perhaps you need a
WHERE clause instead of