Tech Sharing Blog

All the web developer, web admin, as well as DB admin are always facing the attacking from all around the world toward their web site, but how to prevent it?

Some may use the 3th party software to prevent it, some may just filter all the possible keyword enter by the user, some will use the database store procedure to prevent it. The following are some of the keyword that you may need to take care of when you allow your user to enter any input to your system.

Web site injection attack keyword (204)

Popularity: 4% [?]

So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don’t know what to do next. Or maybe you simply want to know of some design guidelines for solutions using SQL Server and designing Database Access Layers (DAL): this article is for you.

Popularity: 1% [?]

There are a lot of web site attacking method now a days, one of the most attacking method that we can easily found is the SQL Injection.

First of all, before you know about the SQL Injection, you may need to know what is SQL. Structured Query Language (‘SQL’) is a textual language used to interact with relational databases.

There are many varieties of SQL; most dialects that are in common use at the moment are loosely based around SQL-92, the most recent ANSI standard. The typical unit of execution of SQL is the ‘query’, which is a collection of statements that typically return a single ‘result set’. SQL statements can modify the structure of databases (using Data Definition Language statements, or ‘DDL’) and manipulate the contents of databases (using Data Manipulation Language statements, or ‘DML’). In this paper, we will be specifically discussing Transact-SQL, the dialect of SQL used by Microsoft SQL Server.

SQL Injection occurs when an attacker is able to insert a series of SQL statement into a ‘query’ by manipulating data input into an application.

The attacker may use the try and error method to study the database table structure and may inject the statement either to read, delete, edit your data in database or more  dangerous is they may take control ovre your database server.

The following PDF file explain the SQL injection in more detail, you may download, study and understanding the most common SQL Injection technique use by the attacker.

Advance SQL Injection (232)

Popularity: 1% [?]

I try to google, call, msn my friend to find out the solution when I stay back in my office until late night yesterday. But still no luck after few hours of trying.

Thinking of gave up when back to office this morning, but one of my friend suddently send me the solution via MSN. That’s the best present I received by today. I’m not sure how many people will looking for this kind of solution, but any way, will post here to share with all and you may take it if you need it.

Basically the idea is that you put all your select, update or delete statment into the variable and use the execute command to execute the whole variable.

My problem:

I have a formula store in a table which is A+B*C+D. My A = 1, B=2, C=3, D=4.

I managed to replace all my variable with the integer using cursor (This maybe not a good solution, but this is not the main discussion in this post)

The finally answer I get is 1+2*3+4. But the bad thing is in the varchar datatype. I only manage to disply 1+2*3+4 but not the answer of 11.

The solution as below:

Declare @MathFomula Varchar(100)

Set @MathFormula = ’1+2*3+4′

Exec(‘SELECT ‘ + @MathFormula  + ‘ AS Result’

Output: 11

* Remember that * and / will calculate at first before + and -

Popularity: 3% [?]

I just found out this usefu function in MSSQL when I try to Google for the solution to replace the null value with something else. So that I no need to do the checking again in my application. The function is call

NULLIF and ISNULL

This 2 functions are actually work opposite with each other.

NULLIF(ColumnName, ValueToCompare) accept 2 parameters, and will return the NULL value if both the expression match.

IFNULL(ColumnName, NewValue) accept 2 parameters, the first parameters is the string that you wish you check with, and the 2nd parameter that you wish to replace with.

Popularity: 3% [?]

In SQL, basically there are two ways to Insert data into a table; one is insert it one row per time, another way is insert multiple rows at a times.

For the first method (insert one row per time) is the most common way to insert data into database that we will use normally, and the sample sql syntax as below:

Insert into table1 (col1, col2, col3)
values (value1, value2, value3)

The other insert method that we seldom use, but it may be useful if you like to copy and insert more then one row of record into a table at a time. You may include the Select statement to specify the data that you want to insert into the tables.

The SQL syntax as below:

INSERT INTO table1(col1, col2, cols3)

SELECT value1, value2, value3
FROM table2
WHERE value4 = 1998

There is another way of writing the same SQL:

Select value1, value2, value3

Into table1

From table2

WHERE value4 = 1998

Popularity: 1% [?]

Normally to get the number of row for a particular table, where we will do is as the following query:

select count(*) from Table_name

Yes, you may get your result very fast if your table is very small, but if your table is more then 100k rows, then you may face some delay on that. What the above query do is actually it run the full table scan to get the row count. The scaning can be Logically or Physically. Logically mean that the SQL server only scan from the catch memory, whereas physically scan mean that it really scan through your table.

Actually there is anther alternative way to get the total row count for a table. You can ultilize the sysindexes system table for this purpose.

There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(‘table_name’) AND indid < 2

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.

The example:

SET STATISTICS IO ON

GO

SELECT count(*) FROM tbTest

GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

GO

SET STATISTICS IO OFF

GO

---

Result:

———–

10000

(1 row(s) affected)

Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.

rows

———–

10000

(1 row(s) affected)

Table ‘sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Popularity: 1% [?]

I believe all the DBA know that how important is “Index” in the database if you would like to optimize the database server performance. It maybe a pain for your server if you create the index wrongly.

Here are some check lists on the index optimization that you should bear in mind and should go through. This is the foundation of the index optimization but it mean and help you a lot, if you already follow and apply all the checklist below, then you may need to try out more experiment to get more optimize:

Index optimization checklist:

• Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on columns which are used in JOIN Condition.
• Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of the entire insert, updates, deletes to the table.
• Smaller Index Key gives better performance than Index key which covers large data or many columns
• Index on Integer Columns performs better than varchar columns.
• Clustered Index must exist before creating Non-Clustered Index.
• Clustered Index must be created on Single Column which is not changing and narrow in size. Best candidate is primary key.
• Non-clustered Indexes increases performance of the query that returns fewer rows and rows has wide selectivity spectrum.
• Each table must have one Clustered Index.
• If column have low selectivity avoid creating Index on that column as it slow down the rows modification and system will not get benefit from Index.
• Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
• Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
• Rebuild Index frequently using ALTER INDEX and De-fragment Index to keep performance optimal for Indexes.

Popularity: 3% [?]

This whole morning just spent about 1 hours+ just to try out one very simpel query on MS SQL 2005.

I keeping get the null value whenever i run this query, even in my program or run directly from the MS SQL 2005 management studio.

Finally I found out that is you are concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL.

With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, String + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is String?

This is the case 1

Update tableA
set field1 + ‘My value’
where field2 = 1

This is the case 2

select ‘test ‘ + null + ‘ string’

returns
————
NULL

This is the solution

set concat_null_yields_null off
select ‘test ‘ + null + ‘ string’

returns
————
test string

Popularity: 2% [?]

Good news for the 4D lover, now you can check all the 4D result and statistics on 4D2U.com. This is Malaysia’s first and most completed 4D results statistics website.

The free and user friendly interface has make 4D2U.com become most popular websites not only in Malaysia, but also other country like Singapore.

You may check your result here after the official result released. Besides that, you may also check the result using the SMS services, just key in the short code as show in the website.

Click here to go to 4D2U.com

[More]

Popularity: 7% [?]