Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's BlogTom Muck's Blog

News and Views

SQL hacking on the web

Tuesday, August 07, 2007 7:32:00 PM

There has been a new rash of SQL injection attacks originating from the far east and other places using the following types of attacks:

somevariable=1%20and%201=convert(int,(select%20top%201%20username%20from%20adminusers))

or

somevariable=1%20and%201=convert(int,(select%20top%201%20char(97)%2bpassword%20from%20adminusers))

or an attack specific to SQL Server:

somevariable=convert(int,(select top 1 table_name from information_schema.tables))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints,syssegments)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints,syssegments)))--sp_password

The first problem was an exploit of the user's default error handling page -- if no error handling is in place, the error message might contain the username, password, or other information:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value 'yourpassword' to a column of data type int. <br>The error occurred on line 102.

In the real attack, the user password was shown on the page. The password was prefaced with the letter "A" -- the char(97) in the attack. This is in case the password started with a number. This can be prevented by using <cfqueryparam> or other device specific to your programming language to make sure integer values are passed as integers.

The second problem is that the default web database user has access to tables that should never be accessible to the web. The malicious user was able to obtain table information from information_schema.tables, and work from there, systematically building each time on information that was previously obtained.

The best possible scenario is to turn off all table access to the web and only access data through stored procedures. That is not always possible. At the very minimum, only expose the data necessary for the site, and only allow access to statements that are required for operation of the site. For example, if you have a table called "Payments", and this is only available to admins, create two SQL username/password logins and use one for the publicly accessed site and one for the admin section. Turn off all permissions to the "Payments" table for the web user. Create "SELECT" permissions only on tables that only need to have data displayed.

As a DBA (which you are if you have a web site with a database and you are the person responsible for the database), you need to know how to secure your data. That involves setting up specific database users for specific access. If a web host gives you a dbo user for a specific database, do not under any circumstance use this username for your web site. This user can be used to create web user logins with specific access. MySQL has similar security features. Access users are out of luck.

The other key is never displaying error messages to users. Make sure your error handling page only shows a pretty message to the user with no information in it, like "You've created an error. Go back and try again." Or prettier than that.

And don't use words or letters for username/password combinations. Passwords should be 10 characters or more, and contain letters, numbers, and special characters. Brute force password guessing programs can figure out a password quickly if you use English language words or just letters.

I'm getting these attacks on my site too. It's scary sometimes having a web site, but hopefully there are safety measures in place to keep these parasites out.

Category tags: Dreamweaver, ColdFusion, SQL, Cartweaver, PHP

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2022 Tom Muck | Dreamweaver Extensions