Specify timeout in connection strings

Here is a quick way to specify the connection command timeout in the connection strings.

Use the property Connect Timeout  and specify a value to it. Here is an example of how this will look like in your application configs in asp.net

<add name=”MyConnString” connectionString=”Server=myserver; Database=mydb; User=sa; Password=mypass; Connect Timeout=20;” />

This is howevre only applicable to Sql Server connection strings.

Make Input and Select as Readonly

I have using asp.net controls for a long time and hence seldom need to use the html controls directly on a webpage. However today working on a project where I was emitting the web page (html) in the Page Render method I was emitting the core html controls. There was a need to make the control readonly or disabled as many of us call it more often, including myself. So I used the attribute disabled and added to the input control. Like this

<input type=”text” value=”whatever” name=”control1″ disabled/>

Now this will surely make the control disabled and readonly. However the funny thing is a disabled control doesn’t pass the value in it back to the server on submit. So if you analyse Request.Params["control1"] on postback or submit it will be empty. In order to get the value on postbacks and make the control readonly at the same time use the attribute Readonly instead. This will ensure the textbox retains its values on the submit.

So the correct way to do is

<input type=”text” value=”whatever” name=”control1″ readonly/>

However things are not simple when using the Select control. <SELECT …> does not have a Readonly attribute. The reason is that technically <SELECT …> lists don’t have values… they have selected options, which themselves have values.  So one way is to do the way Claus has suggested here. However the correct way is to display the value in an input tag using the attribute Readonly.

Happy Coding.

Session Management with a single sign on server

1. Designing it with 
2. CAS – Central Authentication server
3. It needs to have three components
   • login url
   • authentication url
   • logout url
4. All the systems needs to have a login link, where the users can enter their credentials
5. The systems needs to call on every single request an authentication url, which will return true or false indicating that the user has been authenticated
6. The CAS needs to do the session idle timeout. As soon as the request is answered the idle timeout is refreshed
7. The logout URL logs the user out of all the systems.
8. The CAS shall send a cookie with a ticket for authentication, and then all systems must send this ticket back to CAS. The purpose of the cookie is automatic re authentication. This cookie must not be a persistent cookie, i.e. it expires as soon as the user closes the browser. Also called in-memory cookie
9. It is with help of this cookie that CAS will achieve single sign on across multiple applications without prompting the user to enter the log in details again and again. Without the cookie the user enters the username and password again and again, whenever he is redirected to CAS.
10. The cookie expire time is reset whenever it is send back to the server. And hence the session is maintained for 20 minutes or so. As soon as the cookie is expired the session is timed out. This will take care of the inactivity timeouts.
11. When CAS receives the authentication ticket through the validation URL, it checks to see in its internal database if it issued this ticket in the past.

Are stored procedures safe against SQL injection?

To understand why use stored procedures in your application, refer this great article. One of the benefits of using stor procs is preventing SQL Injections. There is a nice article on wiki explaining what SQL Injection is. The first part of this post talks about how parameterized queries and stored procedures can help prevent sql injections. the second part will critically analyse whether so acclaimed stored procedures do prevent sql injections.Dynamic SQL query i.e. sql strings embedded in the code, which are formed without properly validating the user inputs are almost 100% vulnerable to SQL injection attacks. Examine this code fragment -

string username = Textbox1.Text;

string query = “SELECT [name], [address] FROM USERS  WHERE [username] = ‘” + username + “‘”;

This code is expected to fetch the user details, based on username. It can be a typical code listed on user maintainence screen. Now a malicious user can input in the textbox badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%
This input renders the final SQL statement as follows:

SELECT [name], [address] FROM USERS  WHERE [username] = ‘badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%’

We can see how a simple harmless query can result in big threat to your database. Parameterized stored procedures can go a long way in protecting your database applications from SQL Injection. Given no input validation, the parameterized stored procedure still does not allow you to gain access to the site.
But sometimes badly written stored procedures do not prevent injections. The important thing to do is use parameters with stored procedures. SQL injection is possible if the dynamic SQL inside the stored procedure is not handled properly. Let us see an example. 
 

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where [username] = ”’ + @username  + ””

EXEC (@sql)

In the above case, the variable @username is directly taken from the user input and concatenated with the string i.e. @sql. The EXEC function is being used which takes string as parameter to execute the SQL statements. Making this stored procedure vulnerable to SQL injections even though the user inputs are passed to it as parameters. The user input is enclosed in the single quotes and concatenated to a string to form SQL query. The problem lies here. Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘% then the final SQL query executed at the server will be

SELECT [name], [address] FROM [USERS] Where [username] = ‘badguy’;DROP TABLE USERS;  SELECT * FROM Countries WHERE name LIKE ‘%’

The user gets no benefit of the parameterised sql. The safer way to execute a dynamic sql in the stored procedure is

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where [username] = ‘

SELECT @sql = @sql + ‘ [username] LIKE @username’

EXEC sp_executesql @sql, N‘@username varchar(200)’, @username

Why is this stored procedure different and safer from the previous one?

1. The user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement.
2. The function sp_executesql is being used to execute with the parameter list and the parameterized SQL statements.

Measures to avoid SQL injection

1. Validate all input coming from the user on the server.
2. Avoid the use of dynamic SQL queries if there an alternate method is available.
3. Use parameterized stored procedure with embedded parameters.
4. Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
5. Use a low privileged account to run the database.
6. Give proper roles and privileges to the stored procedure being used in the applications.

This posting is provided “AS IS” with no warranties, and confers no rights. 

Last day at Elcom

Today was my last day at Elcom. This was my first job after moving to Sydney. Elcom is a fantastic company to work in. I had a great time and learned a lot from the team. Working at Elcom was fun. The team is very closely knitted. Guys are very technology focussed and help each other. Elcom had become a family to me. We went out to “pub-lunch” and later in the evening had a few drinks. Pawan, my close friend and our HR/Admin Manager gave a heart filled speech and handed me farewell card signed by team mates. Each and every word in the card reflects their love.

Yesterday I spend an hour with John Anstey, CEO and talked about the new directions Elcom wants to move in. At Elcom we have developed Community Manager(CM) – a world class enterprise content management system. Now John wants to move into Microsoft Office SharePoint Server 2007, develop Silverlight modules for CM and possibly start a Silverlight .Net User group. All the best John. A week back at Elcom, Craig Bailey has joined as new CTO. Craig brings with him his vast knowledge and experience on Microsoft Technologies. Boy, is Elcom thrilled to have him!!! Unfortunately for me for this time I didn’t have much time to work with him. But believe it or not the man has left a deep impression on me.

And for all Elcomites I could not thank you enough for your support and affection. I wish you all the best in your future endevours.