Security Issues in SQL Server

Security Issues

Security, IMSHO, is one of those interesting issues that everyone should be aware of. You cannot store a record or two in a database server without considering the security issues. What if a role is broken and a CC# is stolen? Huh? What if an intruder creates a new account/password pair without having the appropriate permission to do so?
Considering the security issues, however, doesn't mean that you can block every single intruder. I honestly believe that whatever you do to block intruders, there will always be another man on this earth who can break the rule. So, all you can do is just try your best to block most of those intruders - Remember, you cannot block all of them!
Well! The question now is what security has got to do with the SELECT statement. To understand this, you should know that there are 3 types of permissions in SQL-SERVER:

1. Object permission - which involves granting or revoking permission rights to the objects. By objects, we mean data and/or stored procedures. Does a certain user have the appropriate access to a certain database to query for a specific table and/or view? Does she have the proper right to insert, update, or delete a row within a table of a certain database? This is where "Object Permission" comes into play. This kind of data manipulation requires the user to have a class of permission called “Object Permission”.
2. Statement permission - which involves granting or revoking permission rights to create a database and/or table within a database. CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE are 3 kinds of Statement permissions introduced in SQL-Server.
3. Implied permission - which states that the owner of an object has got the permission to control the activities of the object. Once a “foo” user owns a “bar” table, she can add and remove data on the “bar” table. The implied permission also states that the “foo” user can control the way other users work with the “bar” table.

With this information in hand, it is now time to say that granting or revoking those three types of permissions to users is done by database administrators, and therefore a programmer doesn’t care about such permissions. So, what is left to the programmer? Well. This is what we have considered as “Security Issues” and is discussed below.
To start, consider a typical ASP application that is supposed to let an authorized user to login to the system. To develop such an ASP application, we simply create a form containing two edit boxes, one provided for user name entry and the other to get a password from the visitor:

<!—-login.asp file!-->
<form method="GET" action="login.asp">
<table border="0" width="100%" cellspacing="0" cellpadding="5">
    <tr>
        <td width="13%">
            <font size="2" face="Verdana">User ID:</font>
        </td>
        <td width="87%">
            <input type="text" name="UserID" size="20">
        </td>
    </tr>
    <tr>
        <td width="13%">
            <font size="2" face="Verdana">Password:</font>
        </td>
        <td width="87%">
            <input type="password" name="Password" size="20">
        </td>
    </tr>
    <tr>
        <td width="13%">
        </td>
        <td width="87%">
            <input type="submit" value="Submit" name="btnSubmit">
        </td>
    </tr>
</table>
</form>

Considering “Mehdi” as the username and “mypass” as the password, the following URL is generated when the submit button is pressed:
login.asp?UserID=Mehdi&Password=mypass&btnSubmit=Submit
Therefore, to authorize a given user, we can simply write a function, namely, IsAuthorized as shown below:

Function IsAuthorized(szUser, szPassword)
 
        IsAuthorized = False
        oConnection = Server.CreateObject("ADODB.Connection")
        szConnection = "provider=sqloledb; server=myserver; uid=myid; "
        szConnection = szConnection & "pwd=mypwd; database=mydb;"
        oConnection.Open(szConnection)
        szSqlStatement = "SELECT * FROM Login WHERE UserID = '" & szUser
        szSqlStatement = szSqlStatement & "' AND Password = '" & szPassword & "'"
        oRS = Server.CreateObject("ADODB.Recordset")
        oRS.Open(szSqlStatement, _
               oConnection, _
               adOpenStatic, _
               adLockOptimistic, _
        adCmdText())
 
        If Not oRS.EOF Then
            IsAuthorized = True
        End If
 
        oRS.Close()
        oRS = Nothing
 
        oConnection.Close()
        oConnection = Nothing
 
    End Function

Now, let’s take a precise look to what we already wrote. Again, consider an intruder accessing our login page using the following user id: ' OR 1 = 1 --
What does actually happen in such circumstances? To understand this, let’s take a look at our SQL statement constructed in IsAuthorized function based on the above entry:

SELECT * FROM Login WHERE UserID = '' OR 1 = 1 --' AND Password = ''

Since two hyphen mentioned by the intruder indicates a user-provided text (i.e., comments) in SQL-92 standards, whatever comes after those hyphens is not considered as the SQL statement and therefore the above statement is simplified to:

SELECT * FROM Login WHERE UserID = '' OR 1 = 1

Well! You could guess the rest. Since 1 is equal to 1, the above-mentioned query results in all the rows already settled in the Login table. You see the intruder had neither user ID nor password but he has been identified as an authorized member.
In some circumstances, though, it will get even worst when an intruder inserts a pair of ID and password to a table. So what the heck can we do to prevent abusing the system? There are basically 3 ways to prevent such things:

1. Create a procedure, say, sp_is_authorized() to make the actual authorization on database side. This way, the intruder won’t be able to bypass the authorization process as described above. The specified procedure follows:
   
   

   CREATE PROCEDURE sp_is_authorized
      @user_id char(16), @password char(16) AS
      declare @nRet int
      select @nRet = count(*) from Login where
              UserID = @user_id AND Password = @password
      return @nRet
   GO
   

   
   We will talk about this usage of SELECT statement later in this article.
   
2. Rewrite the IsAuthorized function using the ADO parameterized queries:
   
   

   Function IsAuthorized(szUser, szPassword)
    
           IsAuthorized = False
           On Error Resume Next
           oConnection = Server.CreateObject("ADODB.Connection")
           szConnection = "provider=sqloledb; server=myserver; "
           szConnection = szConnection & "uid=myid; pwd=mypass; database=pubs;"
           oConnection.Open(szConnection)
           oCmd = Server.CreateObject("ADODB.Command")
           oCmd.ActiveConnection = oConnection
           oCmd.CommandText = "select * from Login where UserID= ? AND Password = ?"
           oCmd.CommandType = adCmdText
           oCmd.Parameters.Append(oCmd.CreateParameter("UserID", adChar, adParamInput, 16, szUser))
           oCmd.Parameters.Append(oCmd.CreateParameter("Password", adChar, adParamInput, 16, szPassword))
           oRS = Server.CreateObject("ADODB.Recordset")
           oRS = oCmd.Execute()
    
           If Not oRS.EOF Then
               IsAuthorized = True
           End If
    
           oRS.Close()
           oRS = Nothing
           oCmd.Close()
           oCmd = Nothing
           oConnection.Close()
           oConnection = Nothing
    
       End Function

3. Construct your dynamic SQL statement after the given user ID and password is checked against the invalid characters. The most common way to do so is using the Regular Expression component, RegExp, which is introduced with IE 5.0. Since describing it goes beyond the scope of this paper, we could simply leave it to the reader.

The final thing we are going to say in this regard is that the above-mentioned situation is still in charge even when you are using the POST method (instead of GET) to submit your form. I strictly emphasize that using the POST method to submit a form is not still secure if you are not going to apply the above-mentioned conditions. There are several ways that an intruder can POST illegal information to your form, and therefore you cannot count on this, solely. So, what we already mentioned is applied to any form (either submitted by POST or GET method) that processes user input.