More T-SQL

- In T-SQL, string comparison is NOT case-sensitive.
  IF 'asc' = 'ASC' PRINT 'equal'
  ELSE PRINT 'NOT equal'
  -- This will out put 'equal'.

- Construct dynamic query and run it in stored procedure. Example:
 
  DECLARE @cmd varchar(200)
  DECLARE @cond varchar(100) = ' WHERE name=''Mary'''
  SET @cmd = 'SELECT * from Users ' + @cond 
  EXEC (@cmd)

- Add a row number in the returned data set:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users

  Note that if "ORDER BY" is used at the end of the query, it should match the
  "ORDER BY" in the ROW_NUMBER() to be fast and starts from 1. E.g.:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID DESC

  But the following will run slow, and does not start from 1 (actually list the last 10 rows of the returned list):

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID ASC

- Create a unique value of type uniqueidentifier.
  See: http://msdn.microsoft.com/en-us/library/ms190348.aspx

  -- Creating a local variable with DECLARE/SET syntax.
  DECLARE @myid uniqueidentifier
  SET @myid = NEWID()
  PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

- Split a string by delimiter ','.
  Reference: http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/    

  DECLARE @loadTable varchar(100) = 'a,b,c,d'
  DECLARE @pos int
  DECLARE @tblResult TABLE (v varchar(10))
  DECLARE @piece varchar(10)

  -- Need to tack a delimiter onto the end of the input string if one doesn’t exist
  IF right(rtrim(@loadTable),1) <> ',' SET @loadTable = @loadTable  + ','

  SET @pos =  patindex('%,%' , @loadTable)
  WHILE @pos <> 0
  BEGIN
    SET @piece = left(@loadTable, @pos - 1)
    INSERT INTO @tblResult (v) VALUES ( @piece ) -- Add to the batch list.
    SET @loadTable = stuff(@loadTable, 1, @pos, '')
    SET @pos =  patindex('%,%' , @loadTable)
  END
    
  select * from @tblResult 

- Temporary table and in-memory table
  1) Temporary table:
  A temporary table is stored in "System Databases\tempdb\Temporary Tables\".

  To declare a table as a local temporary table, use "#" in front of it.
  To declare a table as a global temporary table, use "##" in front of it.
  See Quick Overview: Temporary Tables in SQL Server 2005.

  CREATE TABLE #MyTable1 ( ID int NOT NULL, name varchar(100) )
  INSERT INTO #MyTable1 (ID, name) VALUES (1, 'Mike')
  CREATE INDEX ix_MyTable1ID ON #MyTable1 (ID)
  SELECT * FROM #MyTable1
  DROP TABLE #MyTable1

  The table will be stored as something like "System Databases\tempdb\Temporary Tables\dbo.#MyTable1____________________________________00000002FBEE".

  The table #MyTable1 stays there after the query is done, unless you call the DROP statement.

  Note that if you don't use the "#", then MyTable1 will be created as a physical table in current database.

  2) In comparison, an in-memory table (table variable) can be used this way:

  DECLARE @x TABLE (ID int NOT null, name varchar(100))
  INSERT INTO @x (ID, name) VALUES ('1', 'Mike')
  SELECT * FROM @x

  Note for this one, you can't use index or DROP statement. The table x disappears after the query is done.

- Use cursor
  DECLARE @ID int
  DECLARE @ct int
  DECLARE cs CURSOR FOR SELECT ID FROM table
  OPEN cs

  FETCH NEXT FROM cs INTO @ID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    @ct = @ct + 1 -- keep count of cycles
    PRINT 'cycle: ' + CONVERT(varchar(20), @int)

    FETCH NEXT FROM cs INTO @ID
  END

  CLOSE cs
  DEALLOCATE cs

- ERROR_MESSAGE() - returns error message when @@error != 0

- exception handling in SQL. E.g.:
  BEGIN TRY
    ...
  END TRY

  BEGIN CATCH
    if @@ERROR <> 0 print ERROR_MESSAGE()
  END CATCH

- OUTPUT
  This returns output of a query. The grammar is a little convoluted though. E.g.:

  Declare @sql as nvarchar(512) 
  Declare @params nvarchar(512) = N'@outParam int OUTPUT'
  Declare @out int
  set @sql = 'select @outParam = count(*) from users'
  execute sp_executesql @sql, @params, @outParam = @out OUTPUT   
  print 'out = ' + CONVERT(varchar(100), @out) -- result is in @out

- Get count of rows in each table:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
  INSERT INTO @TableRowCounts ([TableName], [RowCount])
  EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
  SELECT [TableName], [RowCount]
  FROM @TableRowCounts
  ORDER BY [TableName]

- Show space usage:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128),
                              [Rows] VARCHAR(128),
                              [Reserved] VARCHAR(128),
                              [Data] VARCHAR(128),
                              [Index] VARCHAR(128),
                              [Unused] VARCHAR(128)) ;
  INSERT INTO @TableRowCounts ([TableName], [Rows], [Reserved], [Data], [Index], [Unused])
  EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
  SELECT [TableName], [Rows], [Reserved], [Data], [Index], [Unused]
  FROM @TableRowCounts
  ORDER BY [TableName]

- A Visual Explanation of SQL Joins

- Run query on remote linked server, e.g., return a list of databases.
  SELECT * FROM openquery([linked server], 'select name from sys.databases');

MSSQL extended property

MSSQL extended property is used to store metadata information on database objects (tables, views, columns, indexes etc.).

- Extended Property is NOT case sensitive.
- MSDN: add, drop, update, list extended properties:
  http://msdn.microsoft.com/en-us/library/ms180047.aspx

- list all extended properties:
  SELECT * FROM sys.extended_properties;
- list all extended properties for columns in all tables:
  SELECT major_id, minor_id, t.name AS [Table Name], c.name AS [Column Name], value AS [Extended Property]
  FROM sys.extended_properties AS ep
  INNER JOIN sys.tables AS t ON ep.major_id = t.object_id 
  INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
  WHERE class = 1;
- Show all the E.P. of a specific columns in a specific table:
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'T1', 'column', 'id');
  
Add/drop/update/list the Extended Property of a column:
  
- Show an E.P. of a columns in a table:
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty ('[E.P. name]', 'schema', 'dbo', 'table', 'T1', 'column', 'id');

- Add an E.P. of a column in a table (the IF part is optional check):

  IF NOT Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', 'column', 'id'
  ))
  EXEC sp_addextendedproperty 
    @name = 'caption' 
    ,@value = 'Employee ID' 
    ,@level0type = 'schema', @level0name = dbo
    ,@level1type = 'table', @level1name = 'T1'
    ,@level2type = 'column', @level2name = id;
  GO

- Drop an E.P. of a column in a table (the IF part is optional check):

  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', 'column', 'id'
  ))
  EXEC sp_dropextendedproperty 
    @name = 'caption' 
    ,@level0type = 'schema', @level0name = dbo
    ,@level1type = 'table', @level1name = 'T1'
    ,@level2type = 'column', @level2name = id;
  GO

- Update an E.P. of a column in a table:
  EXEC sp_updateextendedproperty 
    @name = N'Caption'
    ,@value = 'Employee ID must be unique.'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = T1
    ,@level2type = N'Column', @level2name = id;
  GO

Add/drop/update/list the Extended Property of a table:

- The above shows how to add/drop/update/list the E.P. of a column.
  To do the same thing for a table, just ignore the last 2 items for
  level2type/level2name. For the IF part, use NULL for the last two items. E.g.:
  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', NULL, NULL
  ))
  Examples are given below.

- Drop an extended property of a table:  
  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', null, null
  ))
  EXEC sp_dropextendedproperty 
    @name = 'Summary' 
    ,@level0type = 'schema' ,@level0name = dbo
    ,@level1type = 'table' ,@level1name = 'T1';
  GO
  
- Add an extended property to a table:  
  IF NOT Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', null, null
  ))
  EXEC sp_addextendedproperty 
    @name = 'Summary' 
    ,@value = 'T1 table''s summary' 
    ,@level0type = 'schema' ,@level0name = dbo
    ,@level1type = 'table' ,@level1name = 'T1';
  GO  

Add/drop/update/list the Extended Property of a view or view's column:

- Same as table, except that use "view" instead of "table" as level1type.
  
Note:

- Since single quote "'" is used as delimiter, it should escaped
  by "''" if it is used in the value.
- Note that string quoted by "'" is allowed to contain new line character.
  So if a value spans multiple lines, it won't be a problem.

SQL Injection Attack

One of the servers is hacked. The symptom is that javascript code were inserted into some database tables. When users visit the site, the javascript code would invoke remote site scripts, display a faked virus scan and report (might be a dynamic gif image), and ask the user to download and install a virus removal software. Once the user installs the software, his computer will be infected.

A script is written to search and clean the database based on signature (characteristic substring) in the javascript code. SQL Query log is added to record all the executed queries. Nothing was found. The problem was finally identified by checking the web server visit log. There are visit requests where SQL commands such as UPDATE used in query string parameter value. It takes advantage of the fact that two consecutive SQL statements can be executed one after the other. This SQL injection attack achieved its goal. Checking the request parameter before executing the SQL can catch such problems. Use of stored procedure and avoid this vulnerability.

A perl script is written to analyze the visit log files and extract these attack requests. The attacked page and attacker's IP is found. Whois service is used to locate where the attacker's IP is from. Patches were made to the attacked page and the site.

Below is the perl script to analyze web server visit log. It extract queries using the 'update' command. Actually it's found that command 'select' is also used in other queries. Can change the script to extract those as well.

#!/usr/bin/perl

#
# @Author: Tom
# @Created on: 9/30/2010
# @Last modified: 9/30/2010
# @Usage example: perl Analyzer.pl < ex100920.log > 100920.txt
# All lines containing the substring "1091+update" are extracted.
#

use strict;

my @fields;
my $fields_ct;
my $line_ct = 0;
my $attack_line_ct = 0;
my @attacked_Pages;
my @attack_IPs;
my $v;
my @vs;
my $i;
my $prefix;

my @ascii_table = (
"NUL",
"SOH",
"STX",
"ETX",
"EOT",
"ENQ",
"ACK",
"BEL",
"BS",
"HT",
"LF",
"VT",
"FF",
"CR",
"SO",
"SI",
"DLE",
"DC1",
"DC2",
"DC3",
"DC4",
"NAK",
"SYN",
"ETB",
"CAN",
"EM",
"SUB",
"ESC",
"FS",
"GS",
"RS",
"US",
" ",
"!",
"\"",
"#",
"\$",
"%",
"&",
"'",
"(",
")",
"*",
"+",
",",
"-",
".",
"/",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
":",
";",
"<",
"=",
">",
"?",
"@",
"A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z",
"[",
"\\",
"]",
"^",
"_",
"`",
"a",
"b",
"c",
"d",
"e",
"f",
"g",
"h",
"i",
"j",
"k",
"l",
"m",
"n",
"o",
"p",
"q",
"r",
"s",
"t",
"u",
"v",
"w",
"x",
"y",
"z",
"{",
"|",
"}",
"~",
"DEL",
);

while(<>) {
  $line_ct ++;
  if (/^\s+$/) { next; } # ignore empty line.
  if (/id=1091\+update/) {} else { next; } # ignore non-attack lines.
  $attack_line_ct ++;

  print $line_ct . ": \n" ;
  print $_ . "\n"; 
  print "Fields dump: \n";
  @fields = split(' ', $_);
  $fields_ct = @fields;
  for ($i = 0; $i < $fields_ct; $i ++) {
    print "$i: ";
    $v = $fields[$i];
    if ($i == 6) {
      $v =~ s/\%2B/+/g;
      #print "$v\n";
      @vs = split('\+', $v);
      foreach my $u (@vs) {
        if ($u =~ /^varchar\(8000\)/) {
          print "$u";
        } elsif ($u =~ /(cast\()(char\()((\d)+)\)/) {
          print "cast(" . $ascii_table[$3]; 
        } elsif ($u =~ /(char\()((\d)+)\)/) {
          print $ascii_table[$2]; 
        } else {
          print "$u";
        }
        #print "+";
        print " ";
      }
      print "\n";
    } elsif ($i == 5) {
      if (Page_Exists($v) == 0) {
        push(@attacked_Pages, $v);
      }
      print "$v\n";
    } elsif ($i == 9) {
      if (IP_Exists($v) == 0) {
        push(@attack_IPs, $v);
      }
      print "$v\n";
    } else {
      print "$v\n";
    }
  }
  print "\n";
}

print "$attack_line_ct attack requests found.\n";

my $Page_ct = @attacked_Pages;
print "$Page_ct attacked Page(s) found: \n";
foreach my $p (@attacked_Pages) {
  print "$p\n";
}

my $IP_ct = @attack_IPs;
print "$IP_ct attacking IP(s) found: \n";
foreach my $p (@attack_IPs) {
  print "$p\n";
}


sub Page_Exists() {
  my ($ip) = @_;
  foreach my $p (@attacked_Pages) {
    if ($ip eq $p) { return 1; }
  }
  return 0;
}

sub IP_Exists() {
  my ($ip) = @_;
  foreach my $p (@attack_IPs) {
    if ($ip eq $p) { return 1; }
  }
  return 0;
}

For cleaning the database, an ASP script is written.

'
' Remove injected code from database tables.
' Use signature and full_signature to specify the injected code.
' Use request string "doClean=y" to do cleaning.
' If not use doClean, will only show infected rows.
' Tom 9-24-2010
'
Dim signature, full_signature
signature = "</script>" 
full_signature = "<script src=http://.../...js></script>"

Dim doClean
doClean = false
if request("doClean") <> "" then doClean = true

Response.Write("<p>Use doClean request parameter to do cleaning. ")
Response.Write("doClean = " & doClean & "</p>")
call getTables()

function getTables()
    'Response.Write("test()<br>")
    Dim db, rs, sql, count, tbl, infectedTblCount
    infectedTblCount = 0

    sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE'"
    set db = Connect()
    set rs = ExecuteRS(db, sql)
    
    count = 1
    do while not rs.eof
        tbl = rs("Name")
            Response.Write("Table " & count & ". " & tbl & "<br>")
            if getTableColumns(tbl) > 0 then infectedTblCount = infectedTblCount + 1
            count = count + 1
        rs.MoveNext()
    loop
    
    Response.Write("<p>" & infectedTblCount & " tables are infected.</p>")

    call rs.close()
    set rs = nothing
    call db.close()
    set db = nothing    
end function


function getTableColumns(tbl)
    Dim db, rs, sql, col, str, chk

    sql = "select column_name as Name from INFORMATION_SCHEMA.COLUMNS where TABLE_name ='" & tbl & "'"
    set db = Connect()
    set rs = ExecuteRS(db, sql)
    
    count = 0 ' count of infected columns.
    
    do while not rs.eof
        col = rs("Name")
        chk = checkTblColumn(tbl, col)
        str = str & "<li>" & col & chk & "</li>"
        if len(chk) > 0 then 
            count = count + 1
            if doClean then str = str & cleanTblColumn(tbl, col)
        end if
        rs.MoveNext()
    loop
    str = "<ol>" & str & "</ol>"
    Response.Write(str)
    
    getTableColumns = count

    call rs.close()
    set rs = nothing
    call db.close()
    set db = nothing
end function


function checkTblColumn(tbl, col)
    Dim db, rs, sql, val, str

    sql = "select " & col & " as Name from " & tbl
    set db = Connect()
    set rs = ExecuteRS(db, sql)
    
    count = 0   
    str = ""
    do while not rs.eof
        val = rs("Name")
        if InStr(1, val, signature) > 0 then
            str = str & ("<li>Infected row: " & encodeStr(val) & "</li>")
            count = count + 1
        end if
        rs.MoveNext()
    loop
    if count > 0 then
        str = "<font color='red'>" & count & " rows infected</font>" & str
        str = "<ol>" & str & "</ol>"
        'Response.Write(str)
        checkTblColumn = str
    else
        checkTblColumn = ""
    end if

    call rs.close()
    set rs = nothing
    call db.close()
    set db = nothing
end function


function cleanTblColumn(tbl, col)
    Dim db, rs, sql, val, str

    set db = Connect()
    set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open tbl, db, 1, 2, adCmdTableDirect
    
    count = 0   
    str = ""
    do while not rs.eof
        val = rs(col)
        if InStr(1, val, signature) > 0 then
            rs(col) = replace(val, full_signature, "")
            rs.Update()
            count = count + 1
        end if
        rs.MoveNext()
    loop
    if count > 0 then
        str = "<font color='red'>CLEANED " & count & " rows infected</font>" & str
        cleanTblColumn = str
    else
        cleanTblColumn = ""
    end if

    call rs.close()
    set rs = nothing
    call db.close()
    set db = nothing
end function


function encodeStr(s)
    s = replace(s, "<", "&lt;")
    s = replace(s, ">", "&gt;")
    encodeStr = s
end function

BTW, this is a wiki article on SQL injection. Search on google for SQL injection would bring up much more articles. Good and important to know for web developers, for security concern.