MySQL crash when executing a particuliar prepared request

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
zeltron73
Posts: 20
Joined: Mon 04 Jun 2012 07:46

MySQL crash when executing a particuliar prepared request

Post by zeltron73 » Thu 02 Mar 2017 11:51

Hello,

I encountered a MySQL (5.7.14) crash when executing a request with MyDAC (8.7.27).
Here is the tables' structure :

Code: Select all

CREATE TABLE `table1` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`RECNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table2` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  `ID` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`RECNO`),
  UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table3` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  `ID` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`RECNO`),
  UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
These tables can be left empty.
Now, if you execute these statements with MySQL Workbench, the request is executed with no problem.

Code: Select all

SET @REQ = 'UPDATE table1 t1 INNER JOIN (
		SELECT
			t2_KEY, t3_KEY
		FROM (
			SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM
				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN
				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)
			WHERE
				(t2.ID <> " ")
		) r
		GROUP BY t2_KEY
	) mc ON (t1.KEY = mc.t2_KEY)
SET
	t1.KEY = mc.t3_KEY';
    
PREPARE Req FROM @REQ;
EXECUTE Req;
DEALLOCATE PREPARE Req;
But, when I execute these lines of code, the MySQL service crashes !

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query.SQL.Text := 'SET @REQ = ''UPDATE table1 t1 INNER JOIN ('#13#10 +
    '		SELECT'#13#10 +
    '			t2_KEY, t3_KEY'#13#10 +
    '		FROM ('#13#10 +
    '			SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM'#13#10 +
    '				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN'#13#10 +
    '				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)'#13#10 +
    '		) r'#13#10 +
    '   GROUP BY t2_KEY'#13#10 +
    '	) k ON (t1.KEY = k.t2_KEY)'#13#10 +
    'SET'#13#10 +
    '	t1.KEY = k.t3_KEY'';'#13#10+
    'PREPARE Req FROM @REQ;'#13#10 +
    'EXECUTE Req;'#13#10 +
    'DEALLOCATE PREPARE Req;';

  Query.ExecSQL;
end;
Thanks for your help.
Regards.

zeltron73
Posts: 20
Joined: Mon 04 Jun 2012 07:46

Re: MySQL crash when executing a particuliar prepared request

Post by zeltron73 » Thu 02 Mar 2017 15:03

Hello,

After a deep testing, I discovered that DoS of MySQL is due to user rights...
If you test the prepared request with `root` credentials, the request will execute normally (and that was what I tested in Workbench).

But if you test the same request with another user with less rights (UPDATE, SELECT only), the server crashes.

Sorry for the inconvenience, it's not MyDAC fault ! I will contact MySQL...

Regards.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: MySQL crash when executing a particuliar prepared request

Post by ViktorV » Fri 03 Mar 2017 07:55

Thank you for your interest to our product.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

Post Reply