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.

How to mark Session Cookie Secure

In one of my previous posts I discussed why we need to mark the cookies as secured. It becomes quite essential to mark the forms authentication cookie and the session cookie as Secured because they contain user sensitive information. A quick solution to mark the ASP.Net session cookie (by default asp.net_sessionid) and the Forms authentication cookie (by default .ASPXAUTH) is to write the following code in your EndRequest Event handler. This code can be added in an HttpModule or in your global.asax file.

// this code will mark the forms authentication cookie and the
// session cookie as Secure.
if (Response.Cookies.Count > 0)
    foreach (string s in Response.Cookies.AllKeys)
        if (s == FormsAuthentication.FormsCookieName || s.ToLower() == “asp.net_sessionid”)
             Response.Cookies[s].Secure = true;

Forms Authentication cookie can also be marked secured by setting the requireSSL attribute in the tag in the web configuration file.

One key this to note is if the server has not been setup for SSL and this logic is used, a new session will be generated for each request. Be sure to use this code only when the HTTPS is used on web server.

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

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. 


@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


@username varchar(200) = NULL


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.