 |
The Largest Online Resource for Oracle Articles, Oracle Tips, Oracle Scripts & Oracle Tools!!
|
|
Author: Stuart Mcnold In this article I am going to discuss one of the most critical database security issues called SQL Injections. SQL injections are not actually the problem with SQL but with improperly written applications. “ SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of variables embedded in SQL statements. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. 1 “ In simple words SQL injection is a type of security vulnerability where hackers execute SQL commands on the database server via web browser. Now let's discuss how SQL injections actually work and how they can be overcome. Ways of SQL Injections: • Improper input validation: This type of SQL injection take place when input from user side is not validated. All input from user should be properly validated. If required input is integer then user should not be allowed to enter string values. There should be data type constraints applied on all input handling. Consider the example where user has to enter his id in order to view his current transactions. When he presses OK button the query that runs at the back end shown below where $id is the value entered by the user. $query=”SELECT * FROM TRANSACTIONS WHERE userID=”+$id+”;” What if user with id=1 fills in the form like this User ID: 1; DROP TABLE TRANSACTIONS OK When the user presses OK button then the query at the back end will become $query=”SELECT * FROM TRANSACTIONS WHERE userID=1; DROP TABLE TRANSACTIONS;” This is the worst case where whole table of transactions can be dropped. There is an option that we enclose the input by escaped characters but will this work? Read ahead to see if escape characters are a suitable solution or not. • Improper filtration of escape characters: If user input is not filtered for escape characters then attackers can potentially manipulate the SQL commands sent to the database. Consider the below example where not all users but only members can see the list of featured books from FEATURED_BOOKS table by entering their memberCode. q="SELECT * FROM FEATURED_BOOKS WHERE memberCode = '" + $code+ "';" Although the variable is properly enclosed by escape characters but still it is vulnerable to SQL injections. What if we get $id=m'; SELECT * FROM BOOKS WHERE name LIKE ‘% The resultant query will be $q="SELECT * FROM FEATURED_BOOKS WHERE memberCode = 'm'; SELECT * FROM BOOKS WHERE name LIKE ‘%';" One can concatenate as many queries as he wants and in worst case your records may be updated and even deleted. • Hacking the query string Suppose you are running a cards site. Your allow non-members to access only 50 cards whereas members can access all the cards. When you open the page to view your some particular card you find below URL in the address bar http://www.test.com/cards.php?card=12 From here one can infer that card is some kind of reference. Now if you reload the page with http://www.test.com/cards.php?card=23 URL then a page with some other card is shown. This allows a non-member to access all your cards without becoming a member. • Guessing field names Sometimes attackers use guesses to find out the valid names of the fields. For example entering
Making a number of guesses can help find out a number of valid field names. • Guessing Table names Below input can help guess if the table name is valid or not.
• Invalid Login The most common and simple SQL injection is in login form. User enters his id and password and based on valid or invalid login information access is granted. The SQL statement will be something like this $query=”SELECT * FROM USER WHERE id='”. $id.”' AND password='”.$password.”'”;What if user enters email=x and password=A' OR ‘a'='a then resultant query will be $query=”SELECT * FROM USER WHERE id=' x' AND password='A' OR ‘A'='A'”; The query will be true for ‘A'='A' and access to invalid user will be granted. • Using xp_cmdshell MS SQL Server supports xp_cmdshell stored procedure that executes operating system commands in the context of a Microsoft SQL Server. It is possible that the attacker enter the xp_cmdshell into field value. As a result it will return the list of files in the current directory of the SQL Server process. How to Avoid SQL injections? • Validate the input. Always check if user has entered the correct input into the form. If field requirememnt is integer then user should not be allowed to enter character value and vice versa. • Sanitize the input Allow user to enter valid set of characters only. Below is an example of valid characters abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@.-_+ Forbid user from entering bad values like ; * ‘ “ – select insert update xp_ • Add escape characters. Most of the SQL injections require the use of single quotation marks to terminate the expression. You can replace all single quotes in user input with double quotes. This will greatly reduce the chance of SQL injections. • Enclose the queries in brackets. The query $query=”SELECT * FROM USER WHERE id='”.$id.”' AND password='”.$password.”'”; is vulnerable to SQL injection as shown in previous examples but $query=”SELECT * FROM USER WHERE (id='”.$id.”' ) AND (password='”.$password.”'”); is not. • Convert user input into string and use double quotes in query. Consider the below SELECT query $query=”SELECT * FROM TEST WHERE name=”.$x; On the back end the user input is converted into string value like this $x ='"'.$_POST[‘id'].'"'; Now if user enters a'; DROP TABLE TEST; Our query will take it as SELECT * FROM TEST WHERE name="n1'; DROP TABLE t1" The SQL injection will be failed and no result set will be returned. • Do not show SQL error to user. Rather use such error handling functions that does not show the actual error to the user. For example you can code like this $result=mysql_query($query) or die(“There was an error in processing your request.”); Similarly you can use if(!$result) • Give limited access to user. Use stored procedures to hide database layer from users. References:
More Oracle Articles, Database Articles and DBA Tips 
|
|||
The name Oracle is a trademark of Oracle Corporation. Any other names used on this website may be trademarks of their respective owners