How a Hacker Uses SQL Injection
MySQL and SQL Server database engines (the most common databases used on the Web) use the SQL language to insert, edit and delete records from database tables. When you run a SQL statement on the server, the database executes the SQL code and terminates the statement using a semicolon. Some webmasters build dynamic SQL code from the HTML forms on the website. For instance, a webmaster might build a “select” query using a customer’s input to log in to the website. The following code is an example of a select query in SQL:
SELECT first_name, last_name, account FROM customer WHERE account=’myaccount’
This code retrieves the customer information from the database and returns the results to the login form on your website. SQL injection incorporates strategic use of the starting and terminating string character (the apostrophe), the comments characters (two dashes) and the semicolon. The above code retrieves one record, but a hacker uses SQL injection to retrieve all records from the database. In this example, only users’ first, last and account names are stolen. However, a hacker can steal credit cards, social security numbers and other private data when implementing the hack on the right Web page.
Formatting a Malicious SQL Injection String
Using the example above, a customer enters an account name in a text box, and the name entered is used in the dynamic SQL statement located on the login processing page. For instance, if you type “myaccountname” in a login form, the above query changes to:
SELECT first_name, last_name, account FROM customer WHERE account= ‘myaccountname’
The apostrophes that surround the string tell the SQL database that a string starts and ends with the “myaccountname” characters. A website without SQL injection protection allows the hacker to prematurely terminate the string and execute an additional SQL statement. For instance, suppose a hacker types the following text into a login text box:
‘ ; select * from customer –
The dynamic SQL query sent to the server is:
SELECT first_name, last_name, account FROM customer WHERE account= ‘’; select * from customer –‘
At first glance, it looks like a malformed query. However, the string is terminated early, the semicolon terminates first select statement, and a new SQL query – select * from customer – is executed. The “—“ characters comment out the final apostrophe, so the SQL server does not return an error. In this example, the hacker gains access to all customer records.
A hacker can also delete information. For example, the following code sent from a website terminates the select query and drops (deletes) a table from the database:
‘ ; drop table customer –
The query results are:
SELECT first_name, last_name, account FROM customer WHERE account= ‘’; drop table customer –‘
Dropping a table permanently deletes it from the database, so you must recover your data using a database backup. The website also errors out, so users are not able to sign up, log in or create a new account. This type of execution crashes the website.
Evaluating Your Website for SQL Injection Vulnerability
The first step in evaluating your site is to make a list of submission forms that run SQL code. Each processing page that uses dynamic, inline SQL is vulnerable to SQL injection. Make a list of each page, because these pages are your source for SQL injection attacks. This includes pages written in Microsoft .NET or PHP as well as any other older languages.
Database security is also an issue. A Web application should only use one database username to run queries. If you have several usernames, you must consolidate them into one, so you can keep better control and watch for any hack attempts using server logs.
The Value of Stored Procedures
Using database stored procedures is the most valuable security measure to protect against SQL injection attacks. Stored procedures use variables, which contain the values entered into the Web page forms by your customers. You must implement parameters with your stored procedures to completely prevent SQL injection. The database engine uses the apostrophe as a literal part of the string when you use stored procedure parameters. This is useful, for instance, when you input a last name “O’Leary” when submitting the data to the database. Parameters also include comment characters and the semicolon as part of the query. For instance, if a hacker attempted to input SQL injection using the previous SQL statement example, the following query executes:
SELECT first_name, last_name, account FROM customer WHERE account= ‘ ‘’; select * from customer – ‘
Notice the double apostrophes in the “WHERE” clause. Double apostrophes indicate to the SQL database engine that you intend to insert an actual apostrophe character, and you do not want to terminate the string. Stored procedure parameters always use the literal character, so hackers are not able to insert any extended SQL code to maliciously gain access to table information.
Scrubbing Input Data
Although scrubbing data is not the most efficient method to protect from SQL injection, it is an option for webmasters who are not familiar with stored procedures, and those who do not have time to create stored procedures for each Web processing page. The term “scrubbing” means you remove any malicious characters. Each programming language used on the Web such as VB.NET, C# and PHP contain functions that remove malicious characters before you send the SQL command to the database.
For instance, if a hacker attempts to insert a malicious apostrophe in an attempt to prematurely terminate a SQL string, the “Replace” function replaces the malicious apostrophe with a double-apostrophe. The following example shows you how to scrub data in C#:
string query = userinput.Replace(“’”, “’’”);
The code above changes all single apostrophe characters to double characters, rendering the hackers attempt at SQL injection harmless.
User Permissions on the Database
A SQL Server database requires administrative permissions to run top-level stored procedures. The “xp_cmdshell” stored procedure lets database administrators run command prompt statements or start third-party software applications on the server. For this reason, webmasters should never use the SQL Server sa account, the MySQL root account or an account with high-level permissions to run SQL code on a website database server.
The previous examples showed you how a hacker can steal database table data. However, SQL injection also allows a hacker to gain user access to the server, if the account used to execute the statement has sa or root privileges on the server. For instance, the following code creates a user account on a Windows hosted database server, which the hacker can then use to steal data on the server:
‘ ; xp_cmdshell “net user hackeraccount password /add” –
The above SQL injection code terminates the string sent to the database server, and uses the “xp_cmdshell” stored procedure to add the account named “hackeraccount” on the database server. The hacker can further his efforts and add the new user to the server’s administrator group, gaining full access to the server.
This type of hack leaves the entire website vulnerable to data theft, if the database server runs on the same computer as the website host. For this reason, separating the database server from the website server also improves security on the website.
Protecting Your Website and Database Server from SQL Injection
1. The best security measure for any website owner is to use stored procedures. Even if the webmaster uses a hosting service, the hoster typically offers database management utilities that let you create stored procedures.
2. Scrub any data in your dynamic SQL statements for apostrophes, semicolons and the double-dash character. Replace a single apostrophe with double-apostrophes, so the database translates the code to a literal apostrophe in the string.
3. Create a separate database user account for each website that runs on your servers. Limit the database account’s privileges. Only supply account privileges to the database tables and never assign any administrative privileges to an account that runs from your website code.
4. Avoid dynamic SQL statements. Some website creators set up dynamic SQL statements based on user input. Instead of dynamic statements, create separate stored procedures with input parameters. You can also create dynamic SQL statements within a stored procedure, but this type of statement also leaves your server open for SQL injection.
5. Encrypt sensitive data. If the hacker gains access to the database, encrypted passwords, social security numbers and credit card numbers prohibit the hacker from reading the data. Encryption on any table field that contains sensitive data should be implemented in addition to SSL encryption on the website.
Jerry, 30 June, 2012
Hello Sir,
Great article,
And I just wanted to know which languages, programs OR software are used in latest web designing.
I'm a student of CS, so what are the basic things i should learn for becoming a web designer.
JC, 11 April, 2012
Nice article. I would suggest using a different font for the code so that the single and double apostrophes will be easier to read.
Rajan, 16 February, 2012
Really amazing. I came to know how hackers can inject malicious queries. Thanks for
such good information.
Ray, 3 October, 2011
Great article! I just wanted to add that your website security can affect more than just how your business operates, it can effect how the public views your company. How would your potential customers react if your website is prone to attacks by viruses? It would most likely reflect that you do not take security seriously and have not taken measures to prevent it from happening. For instance, if you have an e-commerce website, your security is of the utmost importance. Should your security become compromised, it could result in lost customers (aka lost revenue). Who wants to shop on a site if their personal information not going to be kept secure? Your website is the public face of your organization, if it is compromised then it will affect your business image adversely. Thanks to sites like http://www.webhostingsearch.com and http://webhosting.reviewitonline.net it's easier than ever to find secure, quality website hosting providers.
Richard, 3 October, 2011
Interesting article. Will most def take a look at my website to see if I can take any precautions.
Thanks!