How to Session time out when computer got ideal

Session time must be set in web.config file

Use tag for session timeout Property
important : Remeber 10000 in seconds....


Use Session_End Event which is available in Global.asax file.....

Repeator Control in Asp.net

The Repeater control is used to display a repeated list of items that are bound to the control. The Repeater control may be bound to a database table, an XML file, or another list of items. Here we will show how to bind an XML file to a Repeater control.

How to set Connetion pooling in Web Config in asp.net

connectionString="Server=XXX;Database=XXX;User Id=XXX;password=XXX;connection timeout=0;Max Pool Size = 100;Pooling = True"

SQL Injection

Provide an example of SQL Injection

A SQL injection attack is exactly what the name suggests – it is where a hacker tries to “inject” his SQL code into someone else’s database, and force that database to run his SQL. This could potentially ruin their database tables, and even extract valuable or private information from their database tables. The idea behind SQL injection is to have the application under attack run SQL that it was never supposed to run. How do hackers do this? As always, it’s best to show this with an example.

SQL Injection Example

SQL injection attacks typically start with user inputs from people filling out a form on a website. A website ‘form’, if you don’t already know, is something you have definitely used – like when you log into Facebook you are using a form to login, where you input your email address and your password.

For our example of SQL injection, we will use a hypothetical form which many people have probably dealt with before: the “email me my password” form, which many websites have in case one of their users forgets their password.

The way a typical “email me my password” form works is this: it takes the email address as an input from the user, and then the application does a search in the database for that email address. If the application does not find anything in the database for that email address, then it simply does not send out an email address to anyone. However, if the applicationdoes successfully find that email address in its database, then it will send out an email to that person with a new password, or whatever information is required to reset the password.

Starting the SQL Injection Process

The SQL that would retrieve the email address in the “email me my password” form would typically look something like this:

SELECT data FROM table WHERE Emailinput = '$email_input'; 

This is, of course, a guess at what the SQL would look like, since a hacker would not know this information since he does not have access to that. The “$email_input” variable is what the user inputs into the form.

Now, the way a hacker would try to achieve his SQL injection is by inputting SQL where the email address would normally go in the form. A hacker would typically start by inserting an email address with a single quote attached to the end. What’s the point of this? Well, let’s suppose that the hacker tries to input an email address that looked like this – pay special attention to the fact that a quote is appended to the end of this email address:

hacker@programmerinterview.com' 

This would mean that the SQL being run would look like this – note that there is now an extra quote at the end of the WHERE statement in the SQL below:

SELECT data FROM table WHERE Emailinput = 'hacker@programmerinterview.com''; 

What does “sanitizing” the user input mean?

Now, what would happen if the SQL above is executed by the application? Well, that really depends on how the application is set up to handle bad SQL. If the application is ready to handle bad input data, then it will do what’s called “sanitize” the input and reject it with an error message saying something like “Incorrect email format”, or something similar.

Sanitizing data is the act of stripping out any characters that aren’t needed from the data that is supplied – in our case, the email address. Once the data is sanitized, then an error message can be returned saying that the data provided was bad.

The error response tells the hacker a lot

However, if the application is not sanitizing it’s input then it will actually run the bad SQL. So, if the application does run the SQL, then we could get something like an HTTP 500 error – note that this is different from the error message he would get if the application was sanitizing its input. The most important thing to note here is that the error message tells the hacker a lot – because it tells him whether or not the application is sanitizing its input. And if the application is not sanitizing it’s input then it means that the database can most probably be exploited, destroyed, and/or manipulated in some way that could be very bad for the application owner.

Let’s say that the hacker now knows that the database is vulnerable, and that he can attack further to get some really good information. What could our hacker do? Well, if he’s been able to successfully figure out the layout of the table, he could just type this code (where the email address would normally go):

UPDATE accounts SET email = 'hacker@ymail.com' WHERE email = 'joe@ymail.com';  

Then, if this malicious code is run by the application under attack, it would look like this:

SELECT data FROM accounts WHERE Emailinput ='Y';

UPDATE accounts SET email ='hacker@ymail.com'WHERE email = 'joe@ymail.com';

Can you see what this code is doing?  Well, it is resetting the email  address that belongs to “joe@ymail.com” to “hacker@ymail.com”.  This  means that the hacker is now changing a user’s account so that it uses  his own email address – hacker@ymail.com.  This then means that the  hacker can reset the password – and have it sent to his own email  address!  Now, he also has a login and a password to the application,  but it is under someone else’s account. 

In the example above, we did skip some steps that a hacker would have taken to figure out the table name and the table layout, because we wanted to keep this article relatively short. But, the idea is that SQL injection is a real threat, and taking measures to prevent it is extremely important.

How to prevent SQL injection attacks?

In our example above, the best way to have prevented the SQL injection attack was by simply having the user input sanitized – for which we also gave a definition earlier. Since we are dealing with email addresses in our example, this means that we should be able to safely exclude certain characters which don’t normally appear in email addresses. Here is a list of characters that would normally appear in emails, and anything else should not be allowed inside the database – the user should just receive an error saying something like “Invalid email address” if he tries to input an email address with any characters other than the ones below:

abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789 @.-_+ 

Substring function in sql server 2005/2008

Split Name

One common task performed in database applications is given a full name, how can

this be split into the first name and last name. In this article, three different methods are discussed to perform this task using three different functions in SQL Server.

It is assumed in this article that the full name is just made up of one first name and one last name.

Using SUBSTRING Function

The first method is the use of the SUBSTRING string function, as can be seen

from the following script:

DECLARE @FullName        VARCHAR(100) SET @FullName = 'John Doe'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1)

 AS [FirstName],   

 SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))

AS [LastName] 

Given the full name of "John Doe", the script will generate the following output:

FirstName    LastName ------------ ---------------- John         Doe 

Aside from the SUBSTRING string function, it also used the CHARINDEX string

function. The CHARINDEX function returns the starting position of a specified

expression in a character string. The CHARINDEX function was used to look for

the space that separates the first name from the last name (CHARINDEX(' ', @FullName)).

If the full name only contains the last name, the following error message

will be encountered:

Server: Msg 536, Level 16, State 3, Line 4 Invalid length paramete

r passed to the substring function. 

To avoid this error message, the script above needs to be modified to check

if there's a space in the full name. If there's no space in the full name, it is

assumed that it only contains the last name. The following script overcomes

this problem:

DECLARE @FullName        VARCHAR(100) SET @FullName = 'JohnDoe'

SELECT SUBSTRING(@FullName, 1,

NULLIF(CHARINDEX(' ', @FullName) - 1, -1))

AS [FirstName],      

SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1,

LEN(@FullName)) AS [LastName] 

The only difference between this script and the previous one is the addition of the

NULLIF function. The NULLIF function returns a null value if the two specified

expressions are equivalent. In the CHARINDEX function, if the space is not

found in the full name, it returns a value of 0. Then subtracting 1 from this gives

a value of -1. Using the NULLIF function, we are changing the length parameter

passed to the SUBSTRING function to NULL if the value returned by the

CHARINDEX minus 1 is -1. In the SUBSTRING function if the length parameter

is NULL, the string returned is NULL as well. Therefore, if there's no space in the

full name, the first name will have a value of NULL, as shown in the following result:

FirstName    LastName ------------ ---------------- NULL         JohnDoe 

Using LEFT and RIGHT Functions

The second method of getting the first name and last name from a full name is

using the LEFT and RIGHT functions. The LEFT and RIGHT functions are basically

the same as the SUBSTRING function. The LEFT function returns the part of a character

string starting at a specified number of characters from the left while the RIGHT

function returns the part of a character string starting a specified number of characters

from the right.

The script to split the full name string into the corresponding first name and last name using the LEFT and RIGHT string functions is as follows:

DECLARE @FullName        VARCHAR(100) SET @FullName = 'John Doe'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName],

    RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName)) - 1) AS [LastName] 

In getting the first name, it is basically the same as the first version using the

SUBSTRING function. The only difference is that the LEFT function doesn't need the

starting position and it automatically starts at the first position.

In getting the last name, the RIGHT string function is used. The second parameter of the RIGHT function is the number of characters to extract from the full name starting from the right. To get the number of characters to extract, the CHARINDEX is again used. The only difference between the CHARINDEX used in the LEFT string function and RIGHT string function is the addition of the use of the REVERSE string function. Since we want to extract all characters starting from the space until the end of the string, we have to REVERSE the string and count the number of characters from there (CHARINDEX(' ', REVERSE(@FullName)).

To avoid the same error message discussed on the first version when there's no space in the full name, the following script can be used:

DECLARE @FullName        VARCHAR(100) SET @FullName = 'JohnDoe'

SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) - 1, -1)) AS [FirstName],  



  RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1),          

LEN(@FullName))) AS [LastName] 

It uses the same logic used in the first version which is the use of the NULLIF function to check if there's a space in the full name and return a NULL value for the first name if there's no space. As for the last name, it uses the NULLIF and ISNULL functions to return the full name as the last name if there's no space in it. This is done by passing the length of the full name as the second parameter to the RIGHT string function if there's no space in the full name.

Using PARSENAME Function

The last method in splitting a full name into its corresponding first name and last name is the use of the PARSENAME string function, as can be seen from the following script:

DECLARE @FullName        VARCHAR(100) SET @FullName = 'John Doe'

SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName], 

   PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName] 

The PARSENAME string function returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name. In using the PARSENAME string function, we are tricking SQL Server into thinking that the value being passed to the first parameter, which is the object name, is an object within SQL Server since the function does not validate whether or not an object by the specified name exists.

The name of an object in SQL Server is composed of the following parts, separated by a period (.):

[Server Name].[Database Name].[Owner Name].[Object Name]

The second parameter of the PARSENAME function relates to each part of the

object name, each of which having the following values:

• Object Name - 1
• Owner Name - 2
• Database Name - 3
• Server Name - 4

In using the PARSENAME function to split the full name, we need to replace the space with a period (REPLACE(@FullName, ' ', '.')). Then to get the first name, we pass a value

of 2 to the PARSENAME string function, which corresponds to the [Owner Name]. To get the last name, we pass a value of 1 to the function, which corresponds to the [Object Name]. Even if there's no space separating the first name and last name, no error message will be encountered and a NULL value will be returned for the first name while the whole full name will be returned as the last name.

Inserted and Deleted table in sql server

create table Billings (
BankerID INTEGER,
BillingNumber INTEGER,
BillingDate datetime,
BillingTotal INTEGER,
TermsID INTEGER,
BillingDueDate datetime ,
PaymentTotal INTEGER,
CreditTotal INTEGER
;
GO

INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
GO

(1 rows affected)
INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321.);
GO

(1 rows affected)

CREATE TRIGGER trStudentsEcho
ON Billings
FOR UPDATE, INSERT, DELETE
AS
SELECT * FROM inserted
SELECT * FROM deleted
GO

drop trigger trStudentsEcho;
GO

drop table Billings;
GO

Trigger's In sql server

A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.

Creation of Triggers

Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked.

To drop Trigger one can use DROP TRIGGER statement.

Code: SQL

Trigger rules and guidelines

A table can have only three triggers action per table : UPDATE ,INSERT,DELETE. Only table owners can create and drop triggers for the table.This permission cannot be transferred.A trigger cannot be created on a view or a temporary table but triggers can reference them. A trigger should not include SELECT statements that return results to the user, because the returned results would have to be written into every application in which modifications to the trigger table are allowed. They can be used to help ensure the relational integrity of database.On dropping a table all triggers associated to the triggers are automatically dropped .


The system stored procedure sp_depends can be used to find out which tables have trigger on them. Following sql statements are not allowed in a trigger they are:-

Code: SQL

ALL CREATE statements,ALL DROP statements,ALTER TABLE AND ALTER DATABASE,TRUNCATE TABLE,GRANT AND REVOKE,UPDATE STATISTICS,RECONFIGURE,LOAD DATABASE AND LOAD TRANSACTION, ALL DISK statements,SELECT INTO

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

Syntax

Code: SQL

IF UPDATE

CREATE TRIGGER trigger1 ON member

FOR UPDATE

AS

IF UPDATE(last_name)

BEGIN

RAISEERROR('Transaction cannot be processed')

ROLLBACK TRANSACTION

END

Multi-row trigger

A multi-row insert can occur from an INSERT with a SELECT statement.Multirow considerations can also apply to multi-row updates and multi-row deletes.

Code: SQL

CREATE TRIGGER adult_insert ON adult

FOR INSERT

AS

DECLARE @rcnt int

SELECT @rcnt = @@rowcount

IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)=0

BEGIN

PRINT 'Transaction not processed'

PRINT 'No entry for this member'

ROLLBACK TRANSACTION

END

IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)<> @rcnt



BEGIN

PRINT 'Not all adults have an entry in the member table'

PRINT 'Multi-row insert transaction has been rolled backed'

ROLLBACK TRANSACTION

END