Tag Archives: SQL

KIK Project Development

Right now i'm stuck with the database design with a lot of question in my mind. How many table, the attribute, the primary key, index, relationship etc much be considered when designing the database. And the main key right now, it's been  a while for me didn't touch the mysql and the sql command. Need to refresh back the old memory and skills.

Assignment – Database – SQL Injection in Web Application System

SQL Injection in Web Application System

Ahmed Noor Kader Mustajir Md Eusoff

Faculty of Information Management

Universiti Teknologi MARA

Abstract:  SQL injection has been major issues and problem to the web developer that developed web base application and website. Some of the problem can be avoid if the administrator aware of the security holes in their SQL statement and they fixed the vulnerabilities before being manipulate by the hacker or attacker to gain access to modify the system information. These attacks have made the organization loss millions and also the effort done and also their integrity to the client. Counter measurement have been propose to reduce the attack even cannot totally stop and hold the attack because of the flaw in the system. Every database system have flaw and SQL statement can be manipulate to inject the malicious code and Trojan into the system.

Keyword: SQL, SQL Injection, SQL Statement, Web Application System, Vulnerability.

Introduction

Structured Query Language (SQL) is the typical language that used to correspond with a relational database. This prototype was initially developed by IBM with Dr. E.F. Codd’s paper title A Relational Model of Data for Large Shared Data Banks as a model.  Its coverage data query and update, schema creation and modification and also data access control. SQL is definite any of two ways, as the letters S Q L, or “sequel”. Both intonations are tolerable, though most skilled SQL user is likely to use the second intonation, according to Plew & Stephens (2002).

SQL is a regular language for right of entry and manipulate database furthermore it can execute queries, retrieve, insert records, update records , delete records, create new databases, create views, create new tables, can set permissions on tables, procedures, and views in addition create stored procedures. SQL have been accepted by American National Standards Institute (ANSI) as a standard and also accepted by International Standards Organization (ISO) in 1987. SQL was implemented in SEQUEL-XRM; IBM prototype in the mid 70’s and then a division of the language employ in the IBM’s System-R. ORACLE became the first commercial Database Management System (DBMS) that have SQL and other commercial product also followed the ORACLE step like SQL/DS, DB2, SYBASE, UNIFY, DG/SQL, INTERBASE and INFORMIX. These trends have made the SQL become the standard for the DBMS or de facto standard, Calero et al (2006)

SQL standard revised in 1989 which few improvement have been made like the referential integrity and SQL2 or SQL-92 published by ISO, complemented after few years later. Calero et al (2006) & Plew & Stephens (2002) state that SQL3 or SQL: 1999 included features object-oriented capabilities, sensitive cursor, user roles, tables’ generalization, recursive query operator and user defined data types. The revised SQL: 2003 version also included new basic data type (multiset, XML, bigint), SQL/XML, extension to make the CREATE TABLE statement, a new MERGE statement and two new sorts of columns (generated and identify). SQL: 2006 revised and included ways of importing, storing and manipulating XML data in the database. The latest revised SQL standard; SQL: 2008 have the features trigger INSTEAD OF, TRUNCATE statement and ORDER BY. The revised have made the SQL function enhanced from time to time according to the needs of the current situation and future.

SQL Implementation

There is lots of application that information stored in the database there is a deficient in test adequacy criteria and test case design procedure specifically design for database program. Mutation approach is another way for SQL queries use as corresponding help for the tester to developing test cases or the base to test automation tools. SQL would be very useful tools for systematically injecting faults in the queries and use these faulty to analysis the effectiveness base on the studies by Tuya, Suarez-Cabal & Riva (2007). These can guidance on test case generation and comparing different assessment for database application.

Libkin (2003) insist that SQL: 2003 have various features which can differentiate from relational algebra which the aggregate function, grouping and arithmetic. Aggregate function is where the command uses to compute like average in a column, others aggregates are MAX, MIN, SUM, AVG and COUNT. Grouping can group the data into values of different attribute and arithmetic allows SQL to apply arithmetic operations into numerical values.

Brass & Goldberg (2006) has investigate classes of SQL queries that syntactically true or correct can be providing unintended result which produce semantic errors. There is a different between syntactic error and semantic errors, whereby syntactic error is in situation the character string entered is not valid SQL statement. Semantic error is the SQL query being done but the result of the query did not produce the wanted result. The result of the query may produce information that may reduce optimization that required by the user.

SQL Injection

Kost (2007) found that majority of application developer underestimate the SQL injection attack. The application developers did not aware or understand the SQL injection attacks. SQL injection vulnerabilities can be done remotely without any application or database authentication because the attacks are simple and easy to execute.SQL have lots of advantages and also have deficiency whereby can affect the performance of the database furthermore the system itself. SQL attack included code injection, SQL manipulation, buffer overflows and function call injection. SQL manipulation is whereby the modification of the SQL statement like operation UNION or WHERE clause to output the unintended result. Code injection or SQL injection where the new SQL statement being inserted into the SQL statement and only worked when multiple SQL statement per database requested supported by the server and these two attack are the common describe attack.

“SQL Slammer” was the worms that infect Microsoft Desktop Engine (MSDE) and Microsoft SQL Server 2000 which exploit the server and cause buffer overflow and cause denial of service attack (DDOS). Hilley (2003) state Slammer worms attack port 1434 and have affected many ISP and organizations in the world. Like the event when one of the sport event website have been infected by malware that infected the Internet Explorer user that do not have Vector Markup Language (VML) patch with Trojan who visit the website. The hackers exploit the website by injecting the SQL injection vulnerability (SQLIVS) because of the auto generated code by Dreamweaver, Ullrich & Lam (2008). The auto generated code generated by the Dreamweaver also affected the JSP, PHP and ASP where the exposure allowed the attacker to insert SQL injection into the website. The attackers use the SQL injection to alter the information or data of the database and this leads to website defacement. Website defacement is where the attacker attacks the website by altering the visual look of the website and although these attack are harmless but it tarnish the organization image. Some of the website defacement being included Trojan or malware in the server and will attack the user who use or visit the website. These attacks have cost million of lost to the company and user because of the downtime by the server and website. The maintaining need to be done fast and the error need to be troubleshoot again and again to reduced the attack so the server live again to be use. The organization need to accountable for the attack and admit the mistake because of poor maintaining and administration of the system.

According to Gollmann (2008) in the year 2006, SQL injection attack rank number two and these vulnerabilities have attack many major website like MySpace and Gmail. These vulnerabilities being categorize into three categories which naïve execution model, circumvention of the same origin policy and inadequate handling of malicious inputs. The attacks exploit the vulnerabilities at the interface between the backend database server and web server.  Note to reduce the attack can be done by changing the execution model which the primary or roots of the problem. The SQL queries being constructed before input by the user added. Using bound parameter, the query being compile first with placeholder then on the execution of the compiled script will be replace by the actual user input. Stored procedure or lists of parameter sometimes is the alternative ways to avoid the SQL injection but the there will be occur problem at the server backend. The error messages that appear in the system purposely is to help the developer of the system but it also reveal some of the valuable information and structure design of the system of the database to the potential attacker. The reveal information can be use for the attacker to gain more access since of the expose of their system specification and the attacker just need to dig more information from the internet for the security flaws or based on their knowledge from previous experience.

A paper by Thomas, Williams & Xie (2009) stated that 10% reported of total virtual or cyber vulnerabilities were SQL injection vulnerabilities (SQLIVS).  The SQL injection is present when an SQL statement did not keep the input separate and statement structure. The statement input during the runtime send by the application combined with the statement and structure to the database will done the modification to the database data and also structure. They have provided solution to SQL injection by using a prepared statement replacement algorithm to remove the vulnerabilities. PSR-algorithm has removed the threat of SQL injection by moving the minimal manual intervention and does not need to be integrated into the runtime environment system which unlike the other which requires to be integrated to provide solution. The prepared statement generated code produce the same queries for standard data as the original. This PSQ-Algorithm can expand in the future for other solutions and others language as well and also as a technique of implementing the prepared statement to replace the algorithm. These prepared statement reduce the attack but the system administrator need to analyze and make sure the prepared statement will not be use by the attacker and also burden the backend server that contain the database. If the backend server process more than it could the other problem will occur like server overload and might need to reboot or the system hang.

But in the article by Kardkovacs & Tikk (2007) stated that ISA-algorithm did not mind about the uncertainty while transformation procedure is not unsuccessful. It’s creating any possible solution base on the knowledge base that acquired. Whereby if the transformation succeed there will be well formed query result and if not succeed, the query will produced no real uncertainty since there were no substitute to be presented. The algorithm proposed by the author cannot solve expression with symbolic sense or wider, term which assume deeper human knowledge, derivatives of predicate verbs and idiomatic expressions. The most common form of SQL injection attack (SQLIAs) was incorrectly passed parameters, incorrect type handling and incorrectly filtered quotation characters. The attack include the code injection attack whereby the technique input the code into a computer system or program and exploits the vulnerabilities, Mitropoulos & Spenellis (2009). These actions can make the hacker or other user viewing the sensitive data, modified and also destroy the data which also can crash the system. The data destroy by the attacker might be data that valuable to the organization like the client data, organization private information and other which losing the data might threaten the organization to run their business also the trust from their client and potential client for their business.

Morgan (2006) stated that to secure database from SQL injection attack couple preventive measurement can be done to reduce the attack. The counter measurement is by limit the SQL server running with minimal privileges’ access for example not as SYSTEM or as administrator, lock down the SQL server, restrict the SQL server‘s from accessing the file system and the cmd.exe command, only allowed the web application to perform actions from the stored procedure which help to sanity checking the query to prevent the SQL injection, implementing effective parameter validation where its rejecting any query that contain bad parameter and implement effective network level access control. The preventive measurement cannot clean out the SQL attack but it just to reduce the attack and if there were an attack the system administrator must check back their system especially the SQL parameter in their system. The limited access among the best procedure to be taken seriously because it help to prevent the attacker to access another system if there were more than one system in the server. The attacker cannot access the other system because of the limited access and the other systems are not vulnerable to the hacker or attacker.

A research by Huang et al (2003) focus on the SQL injection and cross site scripting vulnerabilities in the research because both of the component exist in many web application or website and the detection and avoidance still considered as difficult for the system administrator. Black-box approach chosen by the researcher to analyze web base application externally without needing the source code (white-box approach) where the white-box used goes together with black-box. Black-box approach tools can perform the analysis and identifying vulnerable sites very rapidly.  To use the tools for SQL injection fault, a reverse engineering must be done first to discover all data entry points. Once the reverse engineering process was done, an attempt to inject the system database with malicious SQL pattern into the server-side program as to manipulate perform of the process of user input to determine the pattern.  It was found that few testing can be done for web application analysis security test like extracting the syntax and semantic input field, indentifying data entry point, injecting malicious SQL injection pattern to test the system, generate valid data for input field, formatting and sending HTTP request and analyzing the replies and most important things is monitor the browser behavior when it perform active content delivered by the web application. The system administrator can look into the system log and can use web analyzer application included in the server. These logs provide the system activity of the server and record the user activity for the use of the administrator as crucial information or tracking the activities of the system and server.

Prevention of SQL Injection

The query from the application is recognized through by combining the characteristic like the method invocation stack race where the query carry out down to the target method, the table and fields that query uses to retrieve the result and the SQL keyword. Combining these three characteristic create a signature to identified the SQL injection. These characteristic make the query sent to the database will be narrow piece down the query and remove the number and also string literals. SDriver is prototype applications that use to prevent SQL injection attack to the web base application. The function of SDriver can associate queries with the website and stored the signatures which the previous stored query to avoid bogus results, Mitropoulos & Spenellis (2009). SDiver provided for free because the code were release under the open source license which enable other user to use it for free and also the user can modified or customize it according to the need and requirement of the user system.  These modifications will fulfill the system needs and help the system administrator to maintain the system more efficient and help the organization functionality with the system.

Fallon, Llewellyn & Smith (2008) stated that in ORACLE have introduced new notation that contained value placeholder and name place holder. Few rules have formed for prevent SQL injection; SQL statement declared as constant and assigning the values for transitional query result. Nested block-statement sometimes needed so the code review easier because the readable by the reader The administrator must understand by SQL syntax template term and know how to differentiate a dynamic SQL syntax as well as static SQL syntax. Defining SQL injection as the implementation of SQL statement with an unintended SQL syntax because every SQL statement executed using dynamic SQL syntax is possible to be exposed to SQL injection. The client must not have direct access to the system but via SQL API also know as control privileges where help limit the attacker access to other system. Design of the system must made thoroughly and the rational of every SQL statement included value placeholder in SQL syntax template where both dynamic SQL and static SQL template. Also use simple name placeholder in the SQL syntax template. Created and use of the ORACLE supplied API’s where designed to execute the SQL statement. The API’s helps the administrator in maintaining the security of the database. These known API’s has advantages because have been burns test by the developer before being release and any update or error the developer will notice the administrator for their action.

SQL injection attack can be reduced by just making programming changes as proposed by Kost (2007). Among idea were bind variable, input validation, function security and error messages. Bind variable where the application coding should be bind in all SQL statement and never concatenating together the string and passed parameter. This bind variable must be use for every SQL statement executed by the web application although this bind variable will added extra line in the coding but as security this won not be matter to the developer. Input validation make every string parameter that passed will be validated. If the system did not use bind variable, special database of character will be remove before send to process the query. SQL injection attack also is done by using the standard and custom database function that have in the application system like by default grant the access to the public. Error message that exist during the execution can be use by the attacker to gain knowledge about the web application itself. Relatively produced the error to the user its advisable the error message produced in the error log where the access to the log only can be gain only by the administrator only.

Conclusion

SQL is very useful in manipulating the data in the database system to retrieve information that require by the user. The manipulating is very useful but certain people especially the attacker or hacker use the vulnerability to take advantages of the web base application for their interest. They use SQL injection attack to extract confidential information or make modification to the database which leads to loss to the organization. Many have reported that the attack have made them lost of millions of money and also the effort made by them. Few counter measurements must be made to reduce even it hard to stop fully the SQL injection attack like use the SQL stored procedure or lists of parameter sometimes is the alternative ways to avoid the SQL injection, hidden the error messages to the user and most crucial is when designing the web application system, everything must took into consideration whether the SQL statement use, bound statement syntax and other things. Usually the system administrator overlook because did not done the analysis thoroughly and look as whole of the system. In ORACLE for example have API’s that help the administrator in designing and embedded it into their system for security. The API’s prevent the user from accessing the system directly by filtering by another application. SQL injection attacks have made lots of lost in money, effort and corporate image or trust to the organization. Even big company including antivirus developer website have these problem and have tarnish their image in where their product purposely developed to stop the attacker but their system being attacked.