Looking for similar PHP's max_execution_time but for MYSQL
Posted by: kuku kuku
Date: February 18, 2012 12:43PM

Hello Dear forum Members,Moderators,Developers and all others!
Since it is actual problem(at least for server administrators) any helps,hints,*tricks* will be usefull.
Ok,Here is my question:
Is here any configuration "directive" for MYSQL 5.5.x something like PHP's
max_execution_time?
Problem in that:
select benchmark('5000000000000','ksjdfndjnfjfjdfdfjgn fjn gf');

query (2-3 such query can overload server,it will eat CPU time plus RAM) so,server will become to overloaded state ASAP.
(BTW,it is more efficient way to DOS servers using 2-3 such queries intead of using Huge BOTNET attack against server)
In other words this is a Denial Of Service (aka DOS) against server(In ex: if malicious user imaged to find sql injection vulnerability in some php script)

Problem in that PHP's
max_execution_time is not enought for prevent this "attack"
You can ask why?
Because query goes to MYSQL Server in this case then PHP script excidees it's maximum execution time but in fact that
malicious "DOS" query still in use (In MYSQL server)(PHP's max execution time nothing does in this case)
And as result your Server will get overloaded(DOS-ed).

You can simply confirm this :After exceeding PHP's maximum execution time(default 30 seconds) login to MYSQL server as root and
issuse
mysql> show full processlist \G
*************************** 1. row ***************************
     Id: 7
   User: root
   Host: localhost:1081
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist
*************************** 2. row ***************************
     Id: 39
   User: somemysqluser
   Host: 192.168.0.15:1071
     db: userdatabase
Command: Query
   Time: 97
  State: executing
   Info: select benchmark(500000000005,'!@$#^&*(_)+|╨н')
*************************** 3. row ***************************
     Id: 40
   User: somemysqluser
   Host: 192.168.0.15:1072
     db: userdatabase
Command: Query
   Time: 43
  State: executing
   Info: select benchmark(900000000005,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAA╨п╨п╨п╨п╨п╨п╨пZ')
3 rows in set (0.00 sec)

mysql> kill 39;kill 40;
Query OK, 0 rows affected (0.01 sec)

You can see that MYSQL query execution still in progress.
You can reproduce this simply for yourself.

Dears,my question:

How I can "LIMIT" that SQL STATEMENT execution time(lets say after 30 seconds) In MYSQL Server itself?
To be more clear: [My Goal is to stop "natively" that mysql query in MYSQL Server after 30 seconds and prevent MYSQL Server overload]
(AFAIK theris no such option in my.ini) or what i'm missing?
If this is not possible how we can mitigate(prevent) this attack against MYSQL server?

Only one thing comes to my mine to write some parser plus attach it to cron(schedule) which will executed every 30 seconds:
Step1: Automatically login to mysql server as root with -e command line option
Step2: issuse show full processlist \g statement
read that data to array
Step3: Do compare =>time<= value against constant ( in eg 30 seconds)
if time['value'] >=30 kill corresponding MYSQL SEssion id (int) automatically issusing kill statement
(mysql -h localhost -uroot -psecretpass -e "kill thatid")
Anyway,i'm looking for native solution :(
(Disadvantage of this method:
mysql root password goes on command line which is security risk
in eg:
wmic process get commandline
can expose mysql root password)

Here is My MYSQL watcher:
#include <File.au3>
#include <Array.au3>

#cs

I'm simple MYSQL watcher.
My goal is to monitor mysql server for malicious queries which sits more than 30 seconds + which can cause Denial of Service
(In eg: High CPU Load,High Memory Load etc.)
(IN ex:         select benchmark(500000000005,'!AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');    )
I'm a Autoitscript file.
(For compile http://autoitscript.com/)
You can compile me to standalaone executable (x64 && x86).
And you can use me on your server.
But always make sure i'm not world readable or writeable plus is not spoofable by users.
Finally i'm For Windows OS.
No need to add me to task scheduler just Add me to Autostart i will loop every 30 seconds and monitor mysql server for
malicious SQL statements and i will kill it for you.

I'm from Scratch and a bit lame :)

Tested on: MYSQL Server 5.5.17 Win (32bit)
1329588060

#ce




Dim $myarr,$arr


;######################################### SPECIFY MYSQL ROOT PASSWORD AND COMPILE ME THATS ALL #######################


$mysqlrootuser='root';
$mysqlrootpsswd='mysqlstrngpass';



;############################################## DO nOT CHANGE ANYTHING BELOW ##########################################


While 1 ;getting to loop
	Sleep(30000) ; sleeping every 30 seconds

$statement='mysql -bA -h 127.0.0.1 -u' & $mysqlrootuser & ' -p' & $mysqlrootpsswd  & ' -e "show full processlist\G"'
;our statement

Run(@ComSpec & " /c " & $statement & '>data.ini',@ScriptDir,@SW_HIDE)
;executing mysql from command line of OS
Sleep(5000) ;sleeping 5 seconds(for filewrite if server high overloaded)

_FileReadToArray(@ScriptDir &"\data.ini",$myarr) ;reading temp file to array
if @error Then
	MsgBox(48,"Boo Boo:(","Unable to read temporary data.ini file (Will be created runtime please make sure i'm in writable directory",20)
	Run(@ComSpec & " /c " & '"' & @ScriptFullPath & '"',@ScriptDir,@SW_HIDE)
	FileDelete(@ScriptDir &"\data.ini"); deleting of our "temporary file"
	; if any error occurs will launch new instance of application then exit from 1'st application.
	Exit
	Exit
	EndIf


FileDelete(@ScriptDir &"\data.ini"); deleting of our "temporary file"



for $i=0 To $myarr[0] ;going to enumerate array elements

if StringMid($myarr[$i],1,8)="   Time:" Then ; searching for time
	;$timevalue=StringMid($myarr[$i],9,-1)
	if StringMid($myarr[$i],9,-1) >=30 Then ;comparing it
Run(@ComSpec & " /c " & 'mysql -bA -h 127.0.0.1 -u' & $mysqlrootuser & ' -p' & $mysqlrootpsswd & ' -e '  & '"kill ' &  StringMid(StringStripWS($myarr[$i-5],8),4,-1) & '"',@ScriptDir,@SW_HIDE)
;and finally killing "malicious" query which sits more than 30 seconds.
		EndIf

EndIf

Next



WEnd


http://pastebin.com/2rj9FdYy


Using:
Windows Server 2003 (32 bit)
MYSQL Server: 5.5.17
PHP: 5.3.10
Apache: 2.2.22.0
RAM: 3 GB
Safe mode on (I know it is deprecated.Enabled 4 fun)
Machine is only for development purposes plus *it is not production server* and it is my *own* machine)

Thanks in advance.



Edited 1 time(s). Last edit at 02/18/2012 12:50PM by kuku kuku.

Options: ReplyQuote


Subject
Written By
Posted
Looking for similar PHP's max_execution_time but for MYSQL
February 18, 2012 12:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.