Second Subquery Inside INSERT Into saves int 0-CodePudding

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

CodePudding user response：

Write your insert as a select, not values.

Untested, but something like:

INSERT INTO `users` (`username`,`password`,`number`,`member_id`,`exp_date`,`notes`) 
select 'balvin','sjeneoeoe','3', 
  (select id from `admin_users` where username = 'TEST'),
  '1644622354', 
  (select pass from `settings`);

Note each sub-query must return a single row.