To understand how to protect itself against injections, we first need to understand how it actually works.
For instance if you have a piece of code like this,
Code:
Dim name = request("name")
odbc.Open "SELECT TOP 1 * FROM [ul] WHERE [chname] = '" & name &"'", __, __
You would normally access this page via something like
page.asp?name=Gregoo, and the SQL server would be indeed sent the correct request
Code:
SELECT TOP 1 * FROM [ul] WHERE [chname] = 'Gregoo'
However, if someone was to access this page via
page.asp?name=Gregoo';DROP%20DATABASE%20[accountdb];--, the SQL server would be sent this
Code:
SELECT TOP 1 * FROM [ul] WHERE [chname] = 'Gregoo';DROP DATABASE [accountdb];--'
(
%20 is an url-encoded space).
This query contains 3 elements.
- SELECT TOP 1 * FROM [ul] WHERE [chname] = 'Gregoo'; the orignal query that we made syntaxically correct with ' at the end. The semi-colon (;) allows us to chain another query after this one
- DROP DATABASE [accountdb]; is our second query and a nasty piece of code that will simply delete a database (in this case, the accounts)
- --' that last part start with a comment (--). Whatever is behind will be discarded. That allows us to keep the query syntaxically correct. If we didn't comment, there would be a quote ' at the end of the query. The parser would detect the error and execute none of the 2 queries.
As of now, we already have one simple way to protect ourselves, that's a basic of security.
You should never give a privileges such as dropping databases, or tables to the database user for the clan.
I've seen pretty much everyone using the sysadmin (
sa) account for the clan files. This user has pretty much
EVERY rights on your database. If you used an user with less privileges you would not have this problem. What the clan user really needs is
SELECT, UPDATE and DELETE. Nothing else.
Using a different user will solve the
DROP problem, but one can still wipe your whole database with the
DELETE privilege. Unfortunately you can't remove it, otherwise players won't be able to leave clans, kick users or disband clans.
The second part of the protection from SQL injections in SQL Server is to simply escape single quotes. We've seen that it's the troublesome character that causes it all.
In SQL Server syntax, you simply need to add a second quote to escape it. If you do that, the injection will just be plain text inside your query and it won't match anything.
If we take the previous example, with escape quotes, the SQL server will receive this
Code:
SELECT TOP 1 * FROM [ul] WHERE [chname] = 'Gregoo''; DROP DATABASE [accountdb];--'
The SQL server will understand this query as it was intended to be, and will look for the data where chname equals
Gregoo'; DROP DATABASE [accountdb];--. As you can see, no more database dropping, and I doubt you have a character one your server with that name. So it'll we simply return nothing.
In my files, in
function.asp you can see the
G (for GET) function doing exactly that.
Code:
' Fetch data from the query string. The data is escaped to prevent SQL injection
' @param key : string
' @return string
Function G(my_key)
G = Replace(trim(request(my_key)), "'", "''")
End Function
- request grabs the values from the URI query string
- trim removes lead/trailing spaces (just to clean up, doesn't do anything for security)
- and Replace( , "'", "''") (double-quote single-quote double-quote, double-quote single-quote single-quote double-quote) adds a single quote in front of every single quote (that's the security fix)
Our piece of code, with the protection, will look like this
Code:
Dim name = Replace(request("name"), "'", "''")
odbc.Open "SELECT TOP 1 * FROM [ul] WHERE [chname] = '" & name &"'", __, __