Looking for similar PHP's max_execution_time for MYSQL

Security issues related to php and mysql usage. How to make your code secure? Security measures and configurations? It's all in here!

Moderators: macek, egami, gesf

TheProdigyGuy
New php-forum User
New php-forum User
Posts: 215
Joined: Wed Dec 07, 2011 5:25 pm

Looking for similar PHP's max_execution_time for MYSQL

Postby TheProdigyGuy » Sun Feb 19, 2012 11:20 am

Ok Guys Copy n pasting my original post from mysql forums.(I hope you will understand me)
Original: http://forums.mysql.com/read.php?35,516 ... msg-516054

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)

For me it is workaround(At least on Windows Box)

Code: Select all

#NoTrayIcon
#include <File.au3>
#include <Array.au3>
#include <String.au3>

#cs

I'm simple MYSQL Server watcher.V2
1)Added logging (default will create logs directory on the same dir with [SELF].exe)
2)More clear way to kill Command: QUERY (ignoring Sleep state)
3) Added Autostart Option.
Uses:(Hardcoded)
Default: MYSQL Server Host: 127.0.0.1
Default: MYSQL Server port: 3306
NOTE*: It will also KILL Persistent connections to MYSQL Server.


Log file format will be:     [*Which is usefull for investigating things*]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ BEOF LOG FILE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

####################################################################################################
Possible DOS Attack Against MYSQL Server [Prevented]
Attack Prevented on: 17:43:48:421 19/02/2012
Attack Duration: 43 seconds
Command: Query
     db: somegranteddatabase
   Host: 192.168.0.15:1075
   User: malicioususer
     Id: 237
   Time: 41
  State: executing
   Info: select benchmark(9999999999999999999999,repeat('AAAAAAAAAAAAAAAAAAAA',100))
####################################################################################################


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ EOF LOG FILE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


My goal is to monitor mysql server for malicious queries which sits more than 10 seconds + which can cause Denial of Service
(In eg: High CPU Load,High Memory Load etc.)
(IN eg:         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 12 seconds and monitor mysql server for
malicious SQL statements (assuming Denial of Service) and i will kill it for you.

I'm from Scratch and a bit lame :)

Tested on: MYSQL Server 5.5.17 Win (32bit)

1329657145

Sorry for my English(not native)

Author: kuku kuku

http://forums.mysql.com/read.php?35,516054,516054#msg-516054

#ce




Dim $myarr; do not remove.


;######################################### SPECIFY VALID MYSQL ROOT PASSWORD AND COMPILE ME. ##################################


$mysqlrootuser='root'; //root user name
$mysqlrootpsswd='YOUR_PASSWORD_GOES_HERE';// root password



$makeautostart=0; // default value=0       (zero)
#cs

$makeautostart is for autostart of application when user logs in.
Possible values:
0     Do not start application when user logs in to system.(You need o start mysqlwatcher manually)
1     Means start application when user logs in.(Automatically)

                         *FOR HIGH SECURITY(i know it is illusion) (IT IS RECOMENDED) TO INSTALL YOUR MYSQL SERVER ON ANOTHER BOX.*
 WHICH WILL PREVENT STEALING OF YOUR APPLICATION(i assume will prevent from Reverce code Engineering-because mysqlwatcher contains MYSQL server root password!)
Here is how i'm doing it:

192.168.0.1 (MYSQL SERVER INSTALLED HERE) and my mysql watcher runs there(it is not connected to internet)(internal network)

192.168.0.15 (Apache+PHP+FTP+MAIL SERVER) installed here + with Software restriction policy(Obviously it is connected to internet)

Using this way you can secure your MYSQL SERVER + mysql watcher from *UNO's* xD)

#ce


;#############################################################################################################################


$killifexceeds=10; seconds  (If query sits on MYSQL server more than 10 (ten seconds) killing it //default value=10 seconds
$sleepinloop=12;   seconds //This is a main loop sleep time.DO not specify it to 0 (zero).Otherwise you can get HIGH CPU LOAD!!!! //defaut value=12 seconds



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

MsgBox(64,"Info","Running MYSQL Server Watcher V2!",2);no need to click "OK" button.It has timeout value 2 seconds(Which means it will disappear after 2 seconds)


$autostartkey="HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run"
$valuename="mysqlwatcherv2"
$type="REG_SZ"
$self=@ScriptFullPath


if $makeautostart=1 Then
   RegWrite($autostartkey,$valuename,$type,$self)
Else
   RegDelete($autostartkey,$valuename)
   EndIf


While 1 ;getting to loop
   if not FileExists(@ScriptDir&"\logs\") Then ; checking if logs directory exists.If it is not exists creating it (We need to keep our log files there)
      DirCreate(@ScriptDir&"\logs\");creating it
      EndIf
   Sleep($sleepinloop * 1000) ; sleeping every 12 seconds // 1 second=1k milliseconds

$statement='mysql -bA -h 127.0.0.1 -u' & $mysqlrootuser & ' -p' & $mysqlrootpsswd  & ' -e "show full processlist\G"'
;our statement
if FileExists(@ScriptDir&"\data.ini") Then FileSetAttrib(@ScriptDir&"\data.ini","-RASHNOT",1)
Runwait(@ComSpec & " /c " & $statement & '>data.ini',@ScriptDir,@SW_HIDE)
;executing mysql client from command line of OS
Sleep(1000) ;sleeping 1 second(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)" & @CRLF & "Please make sure i'm in writable directory" & @CRLF & _
   "Going to Restart Application.Please Wait...",20)
   Run(@ComSpec & " /c " & '"' & @ScriptFullPath & '"',@ScriptDir,@SW_HIDE)
   if FileExists(@ScriptDir&"\data.ini") Then FileSetAttrib(@ScriptDir&"\data.ini","-RASHNOT",1)
   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.(i assume self exit)
   Exit
   Exit
   EndIf

if FileExists(@ScriptDir&"\data.ini") Then FileSetAttrib(@ScriptDir&"\data.ini","-RASHNOT",1)
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
   if StringMid($myarr[$i],9,-1) >=$killifexceeds And $myarr[$i-1]='Command: Query' Then ;comparing it plus ignoring sleep state.Accepting only query-es
Runwait(@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 10 seconds.

Beep(1500,1200); Generating "beep" signal which means possible attack prevented against mysql server.

;######################################                LOGGING        ####################################################################################
FileWrite(@ScriptDir&"\logs\"&@MSEC & '_' &@SEC & '_' & @MIN &  '_'& @HOUR & ' ' & @MDAY & '_' & @MON & '_' & @YEAR & '.txt',@CRLF & _
_StringRepeat('#',100) & @CRLF & _
'Possible DOS Attack Against MYSQL Server [Prevented]' & @CRLF & _
'Attack Prevented on: ' & @HOUR & ':' &@MIN  & ':' &@SEC  & ':' & @MSEC &  ' ' & @MDAY & '/' & @MON & '/' & @YEAR & @CRLF &  _
'Attack Duration:' & StringMid($myarr[$i],9,-1) & ' seconds' & @CRLF & _
$myarr[$i-1] & @CRLF & $myarr[$i-2] & @CRLF & $myarr[$i-3] & @CRLF & $myarr[$i-4] & @CRLF & $myarr[$i-5] & @CRLF & $myarr[$i] & @CRLF & _
$myarr[$i+1] & @CRLF & $myarr[$i+2] & @CRLF & _StringRepeat('#',100))
;logging to log file
;#########################################################################################################################################################


      EndIf

EndIf

Next



WEnd


http://pastebin.com/xkPrfbLa



Any hints,tricks much appreciated + hope that workaround will help to some one too.(especially for Windows Server administrators)


Thanks.

Return to “PHP & MySQL Security”

Who is online

Users browsing this forum: No registered users and 1 guest