SQL Injection

A humohurous illustration of SQL injection via a student's name. Source: xkcd 2007.
A humohurous illustration of SQL injection via a student's name. Source: xkcd 2007.

SQL (Structured Query Language) is a language used to create, update and access data in a database. By carefully crafting SQL commands, a hacker can intentionally cause the application to fail, delete data, steal data or gain unauthorized access. This is what we call SQL injection or SQL Injection Attack (SQLIA). SQL itself is a highly flexible language, which creates opportunities for hackers to acquire sensitive information such as credit card details, user passwords or user IDs. .

There are various methods and tools to attack an SQL database. This article gives an overview of SQL injection attacks and what hackers aim to achieve. We also cover the types and techniques of SQL injection attacks. We note some possible preventive measures towards more secure database-driven websites.

Discussion

  • Why do we need to worry about SQL injection?
    Summary of injection attacks in 2021. Source: OWASP 2022b.
    Summary of injection attacks in 2021. Source: OWASP 2022b.

    CWE (Common Weakness Enumeration) is a system where all weaknesses and vulnerabilities are categorised concerning a particular system architecture, code, hardware or software. CWE-89: SQL Injection notes that any data-rich software is vulnerable to attack when user information is stored in an SQL-based database. An attack can lead to loss of data confidentiality, access control and data integrity.

    Many widely adopted databases are based on SQL including Oracle, MySQL, Microsoft SQL Server, PostgreSQL and SQLite. In April 2022, these were in the first, second, third, fourth and tenth positions respectively. SQLIA techniques may slightly differ across databases but all are vulnerable if not properly secured.

    In 2021, the Open Web Security Project (OWASP) tested 94% of applications under study for injection attacks. They found that 19% had at least one of instance of a vulnerability type (called incidence rate). Injection category was the third-most serious risk and includes 33 CWEs. On a ten-point scale, 7+ values for both exploit and impact shows the threat of SQLIA. In the years 2004/2007/2010/2013/2017, injection attacks were in positions 6/2/1/1/1 respectively.

  • Could you introduce SQL injection with a few examples?
    SQL injection examples and their results. Source: Adapted from OWASP 2021.
    SQL injection examples and their results. Source: Adapted from OWASP 2021.

    The figure illustrates a few examples, each affecting the application in a different way. In all cases, inputs submitted by hackers are directly used to form queries without proper validation.

    The first example possibly represents a user sign-up form where the user is expected to enter first name and last name. Instead, a hacker enters first name that includes a single quote. Since single quote is normally used by developers to enclose values, the extra quote in input causes execution error. The application will not respond correctly to the user.

    In the second example, the hacker submits the string name' OR 'a'='a for item name. In the resulting command, 'a'='a' will match and return all records to the hacker. This is a serious data breach.

    In the third example, using semi-colons, we execute multiple commands, called batched queries. Comment syntax -- is used to ignore characters after the delete command. This attack results in a serious data loss.

  • What's the nature of SQL injection attacks?
    A hands-on demo of SQL injection. Source: Computerphile 2016.

    SQLIA can take many forms: identifying injectable parameters, performing database fingerprinting, determining database schema (table or column names, datatypes), adding/modifying/extracting data, performing denial of service (locking or dropping rows/tables/databases), evading detection, bypassing authentication, executing remote commands, performing privilege escalation, etc. In general, a hacker tries to gather information about the database, uses that information to handcraft attacking commands, and finally execute those commands.

    These attacks can be placed into four categories:

    • SQL Manipulation: Modify SQL statements possibly by changing the WHERE clause.
    • Code Injection: Insert extra SQL statements after the exploitable SQL statement. This is possible on databases that support multiple SQL requests. Semi-colons typically separate multiple queries. UNION statements can be used provided the hacker knows the exact number columns and their data types returned by the exploited statement.
    • Function Call Injection: Insert function calls within the exploited SQL statement. This can not only manipulate data but also make operating system calls.
    • Buffer Overflows: A product of using a function call injection. Hacker exploits a weakness often found on unpatched servers.
  • What are the different types of SQL injection attacks?
    SQLIA types and examples. Source: Adapted from Limpalair 2020.
    SQLIA types and examples. Source: Adapted from Limpalair 2020.

    Different types of SQLIA have been documented:

    • Boolean-based: Aka tautology attack. The use of OR 2 = 2 within the WHERE clause makes other conditions in the clause redundant.
    • UNION-based: The UNION statements combines the results of separate queries. ' UNION SELECT username, password FROM users-- is an example injection. Where multiple columns have be retrieved within fewer columns, columns can be concatenated.
    • Error-based: Error messages can give information about database type. For example, a query that fails due to a SUBSTRING function call suggests that it's probably an Oracle database where the equivalent function is named SUBSTR.
    • Batch Queries: Aka piggyback attack. For example on SQL-Server 2005, ; INSERT INTO users VALUES (‘Abubakar’, ‘1234’);# ends the original statement, appends another statement and comments out any trailing characters with # character.
    • LIKE-based: Similar to tautology attack but based on pattern matching using LIKE and wildcard operator %. An example is OR username LIKE 'S%'.
    • Stored Procedure: Hacker executes built-in stored procedures using malicious SQL code injections.
  • What's a blind SQL injection?

    A hacker typically submits an SQL query and gathers information from the response. Information can come back in the same channel (in-band) or in a separate channel such as email (out-of-band). But in blind SQL injection (aka inferential attack) the hacker obtains useful information even when no data or error message is returned to the hacker. The hacker does this by observing the behaviour. Blind injections can be Boolean-based or time-based.

    With Boolean-based attacks, the hacker sees that true and false evaluations result in different behaviour. He then exploits this, perhaps over many queries, to eventually learn sensitive information such as the administrator's password.

    With time-based attacks, the hacker can insert time delays into SQL command execution. These delays can be conditional or unconditional. Due to synchronous processing, these delays affect the HTTP response to the client. Based on the query, the response time gives the hacker clues to what's going on. For example, an observed delay can confirm that an injected condition was true; or adding SLEEP(1) to the WHERE clause can inform the number of matching records.

  • What's a second-order SQL injection?
    An example of second-order SQL injection. Source: PortSwigger 2022d.
    An example of second-order SQL injection. Source: PortSwigger 2022d.

    Second-order SQL injection occurs when the injected inputs don't do immediate damage. They're simply stored in the database as data. When later retrieved, they end up causing malicious executions. Second-order injections are in a way sophisticated because they bypass prevention techniques that're employed when inputs are validated. However, similar stringent validation may be lacking when data is retrieved from the database.

    The figure shows an example in which a hacker creates a user account with name that hides within it an update command. The system may at best check for length, valid characters and uniqueness of username. But at a later time, when the hacker logs in with this handcrafted username, it executes the update command and changes the administrator password. This effectively gives the hacker much more power to cause serious damage.

    Second-order injections can be used to create tables or functions that can be exploited later when those constructs are triggered. Shared search criteria, website statistics and customer services are some app features that may be exploited to achieve second-order injections.

  • What evasion techniques do hackers use for advanced SQLIA?

    There are Intrusion Detection Systems (IDSs) and tools to detect and protect against common injections. Hackers have therefore evolved techniques to achieve more sophisticated attacks that basic systems may not detect:

    • Encoding: Instead of using plain ASCII characters, injections are URL-encoded. Alternatively, UTF-8 or hexadecimal encoding may be used. For example, 5' OR '5'='5 is encoded as %35%27%20%4F%52%20%27%35%27%3D%27%35; or admin is encoded as char(97,100,109,105,110).
    • White-spacing: Whitespaces are not significant in SQL. Hackers therefore omit or insert extra whitespaces that may include line feeds, carriage returns and tabs. For example, OR '5'='5', OR '5'='5' and OR'5'='5' are equivalent.
    • Comment: Using multiline comments, attackers can subvert detection techniques that attempt to match signatures. For example, a SELECT query can be written as SELE/**/CT or /**/ SELECT/**/.
    • Capitalization: Where the database is configured to be case-insensitive, AND is same as anD.
    • Variation: Techniques in this group include concatenation, variables, and conversions. For example, 'UN'||'ION is same as UNION. A statement is split into multiple variables and combined later for execution.
  • Which tools help in detection and prevention of SQLIA?
    Tools to detect and prevent SQLIA. Source: Adapted from Halfond et al. 2006, tables 1-2.
    Tools to detect and prevent SQLIA. Source: Adapted from Halfond et al. 2006, tables 1-2.

    The figure show tools to either detect or prevent SQLIA, and their handling of different attack types. A 2017 study noted that SQLCheck, SQLIPA, DB IDS, AMNESIA, SQLDOM and WebSSARI all achieve better than 90% protection.

    SQLrand is a proxy server between the web server and the database server. It receives randomized SQL from application and submits standard SQL to the database. All user inputs are treated as non-keywords. AMNESIA does static analysis of application code, constructs a model of valid queries and validates queries at runtime. WebSARRI is another tool that combines static analysis and runtime inspection.

    SQLDOM creates strongly-typed classes based on the database schema. These classes are used to generate SQL statements. It’s an object model as it's used with the help of object-oriented languages. SQLCheck focuses on grammar and policy that are created and stored on the web server. User inputs are augmented with metacharacters and then passed to the checker for validation.

  • What should developers do to protect against SQLIA?

    While a production system may employ tools to detect and prevent SQLIA, there are some best practices that can developers can adopt to mitigate the possibility of SQLIA in the first place. Give users only limited database privileges. Application code probably never needs to drop or truncate tables. Configure web server properly so that verbose error messages are not shown to users.

    Never trust user inputs. Don't rely only on client-side validation since they can be bypassed with curl, postman or similar tools. Do server-side validation. Adopt safe coding practices. Put quotes around input strings. Replace single quote with two single quotes. Where numbers are expected, check that indeed only digits are present.

    Even better is to avoid using raw SQL statements formed from raw input strings. Use custom stored procedures and pass user inputs as parameters into these procedures. Many SQL databases support the PREPARE statement and pass in user inputs as parameters to such a statement. This avoids the unsafe practice of concatenating raw inputs to form queries.

    Read OWASP SQL Injection Prevention Cheat Sheet and OWASP Query Parameterization Cheat Sheet.

Milestones

Dec
1998

Jeff Forristal (alias rain.forest.puppy) writes about SQL injection in the Phrack magazine. He notes that MS SQL Server 6.5 allows execution of batch commands and gives many examples to exploit this. He concludes,

Don't assume user's input is ok for SQL queries.
2003

As more and more web applications emerge, there's a need to find loopholes that may endanger the user's security. In this context, WAVES (Web Application Vulnerability and Error Scanner) is first introduced. WAVES is a project developed by Hunag et al. to create a platform to assess web application security based on vulnerabilities such as SQL injection and cross-site scripting.

2008

A hacker obtains access to 100 million debit and credit cards by hacking into the Heartland Payment System. As a result, Heartland pays around $140 million in fines and penalties.

2011

Multiple attacks targeting Sony through Sony Pictures, Sony Music Japan and Sony Playstation Network occur. Personal information of 100 million users is leaked, causing a loss of $171 million. The attacks on Sony Pictures and Sony Music Japan used SQL injection.

Mar
2011

As many as 47 MySQL databases on Apache web server get hacked. Hackers publicly post usernames, database schemas and passwords that they gathered using blind SQL injection. The breach includes hashed passwords of administrative accounts of webmaster, WordPress, root and forums.

2013

OWASP Top 10 is published stating how injection attacks take top spot among all types of attacks. This top spot is maintained in a subsequent survey in 2017. The report notes that injection attacks are easy while the impact is severe.

2017

In a survey paper, Alwan and Younis mention two recent types of SQLIA called Fast Flux SQLIA and Compounded SQLIA. The latter is a combination of many attack techniques including DDoS attack, DNS hijacking, Cross-Site Scripting (XSS), and more.

Jun
2019

Lewis of NCC Group shares a proof-of-concept of using Machine Learning to detect SQLIA vulnerabilities. They use real-world vulnerability data (though limited in size) and limit their study to only MySQL. They use SVM-Multiclass. In 2021, Erdodi et al. propose using Reinforcement Learning instead with Markov decision process as the model for SQLIA. These are just two examples to illustrate the use of machine learning in SQLIA.

2020
Parse tree for Boolean-based SQLIA. Source: Abikoye et al. 2020, fig. 1.
Parse tree for Boolean-based SQLIA. Source: Abikoye et al. 2020, fig. 1.

Abikoye et al. use the Knuth-Morris-Pratt (KMP) string matching algorithm to detect and prevent SQLIA. Based on syntactic analysis of various types of attacks, parse trees are designed. KMP algorithm is then used to compare user inputs against the known attack patterns.

References

  1. Abikoye, Oluwakemi Christiana, Abdullahi Abubakar, Ahmed Haruna Dokoro, Oluwatobi Noah Akande, and Aderonke Anthonia Kayode. 2020. "A novel technique to prevent SQL injection and cross-site scripting attacks using Knuth-Morris-Pratt string match algorithm." EURASIP Journal on Information Security, SpringerOpen, pp. 1-14, August 18. doi: 10.1186/s13635-020-00113-y. Accessed 2022-03-19.
  2. Alwan, Zainab S., and Manal F. Younis. 2017. "Detection and Prevention of SQL Injection Attack: A Survey." International Journal of Computer Science and Mobile Computing (IJCSMC), vol. 6, no.. 8, pp. 5-17, August. Accessed 2022-03-05.
  3. Boyd, Stephen W., and Angelos D. Keromytis. 2004. "SQLrand- Preventing SQL Injection Attacks." In: Jakobsson, M., Yung, M., Zhou, J. (eds), Applied Cryptography and Network Security, Lecture Notes in Computer Science, vol. 3089. Springer, Berlin, Heidelberg. doi: 10.1007/978-3-540-24852-1_21. Accessed 2022-03-20.
  4. Computerphile. 2016. "Running an SQL Injection Attack." Computerphile, on YouTube, June 15. Accessed 2022-03-10.
  5. DB-Engines. 2022. "DB-Engines Ranking." DB-Engines. Accessed 2022-04-03.
  6. Erdodi, Laszlo, Åvald Åslaugson Sommervoll, and Fabio Massimo Zennaro. 2021. "Simulating SQL Injection Vulnerability Exploitation Using Q-Learning Reinforcement Learning Agents." arXiv, v2, May 22. Accessed 2022-04-29.
  7. Forristal, Jeff. 1998. "NT Web Technology Vulnerabilities." Phrack Magazine, vol. 8, no. 54, December 25. Accessed 2022-03-18.
  8. Halfond, William G.J., and Alessandro Orso. 2006. "Preventing SQL Injection Attacks Using AMNESIA." ICSE '06: Proceedings of the 28th international conference on Software engineering, pp 795–798, May 20-28. doi: 10.1145/1134285.1134416.Accessed 2022-03-20.
  9. Halfond, William G.J., Jeremy Viegas, and Alessandro Orso. 2006. "A Classification of SQL Injection Attacks and Countermeasures." Proceedings of the International Symposium on Secure Software Engineering, March. Accessed 2022-03-22.
  10. Horner, Matthew, and Thomas Hyslip. 2017. "SQL Injection: The Longest Running Sequel in Programming History." Journal of Digital Forensics, Security and Law, vol. 12, no. 2, article 10, pp. 97-108. doi: 10.15394/jdfsl.2017.1475.Accessed 2022-04-06.
  11. Huang, Yao-Wen, Fang Yu, Christian Hang, Chung-Hung Tsai, D. T. Lee, and Sy-Yen Kuo. 2004. "Securing Web Application Code by Static Analysis and Runtime Protection." WWW '04: Proceedings of the 13th international conference on World Wide Web, pp. 40–52, May 17-20. doi: : 10.1145/988672.988679. Accessed 2022-03-24.
  12. J, Abirami, Devakunchari R, and Valliyammai C. 2015. "A Top Web Security Vulnerability SQL Injection attack - Survey." IEEE Seventh International Conference on Advanced Computing (ICoAC), pp. 1-9, December 15-17. doi: 10.1109/ICoAC.2015.7562806. Accessed 2022-02-25.
  13. Kindy, Diallo Abdoulaye, and Al-Sakib Khan Pathan. 2011. "A survey on SQL injection: Vulnerabilities, attacks, and prevention techniques." IEEE 15th International Symposium on Consumer Electronics (ISCE), pp. 468-471, June 14-17. doi: 10.1109/ISCE.2011.5973873. Accessed 2022-03-18.
  14. Kumar, Puspendra, and R.K. Pateriya. 2012. "A survey on SQL injection attacks, detection and prevention techniques." IEEE Third International Conference on Computing, Communication and Networking Technologies (ICCCNT'12), July 26-28. doi: 10.1109/ICCCNT.2012.6396096. Accessed 2022-03-03.
  15. Lewis, Dave. 2015. "Heartland Payment Systems Suffers Data Breach." Forbes, May 31. Accessed 2022-03-25.
  16. Lewis, Matt. 2019. "Project Ava: On the Matter of Using Machine Learning for Web Application Security Testing – Part 6: Development of Prototype #2 – Creating a SQLi PoC." NCC Group, June 18. Accessed 2022-04-29.
  17. Limpalair, Christophe. 2020. "What are SQL Injections (SQLi)? Introduction to powerful techniques." CYBR, August 17. Accessed 2022-04-06.
  18. MITRE. 2021. "CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')." CWE, The MITRE Corporation, October 26. Accessed 2022-02-25.
  19. McClure, Russell A., and Ingolf H. Kruger. 2005. "SQL DOM: Compile Time Checking of Dynamic SQL Statements." IEEE Proceedings 27th International Conference on Software Engineering, May 15-21, pp. 88-96. doi: 10.1109/ICSE.2005.1553551. Accessed 2022-03-21.
  20. OWASP. 2013. "OWASP TOP 10-2013." OWASP. Accessed 2022-03-25.
  21. OWASP. 2021. "SQL Injection." OWASP Community Pages, OWASP, May 26. Accessed 2022-04-28.
  22. OWASP. 2021b. "Introduction - OWASP Top 10:2021." OWASP, November 16. Accessed 2022-04-28.
  23. OWASP. 2022a. "SQL Injection Prevention Cheat Sheet." Cheat Sheet Series, OWASP, April 12. Accessed 2022-04-28.
  24. OWASP. 2022b. "A03:2021 – Injection." Top 10:2021 List, OWASP. Accessed 2022-04-28.
  25. Ollmann, Gunter. 2014. "Second-order Code Injection Attacks: Advanced Code Injection Techniques and Testing Procedures." White paper, NGS Software. Accessed 2022-04-28.
  26. Oracle. 2022. "Examples of Second Order SQL Injection Attack." Tutorial, Oracle. Accessed 2022-04-28.
  27. PortSwigger. 2022a. "Blind SQL injection." PortSwigger.Accessed 2022-04-28.
  28. PortSwigger. 2022b. "SQL injection cheat sheet." PortSwigger. Accessed 2022-04-28.
  29. PortSwigger. 2022c. "SQL injection UNION attacks." PortSwigger. Accessed 2022-04-28.
  30. PortSwigger. 2022d. "SQL injection." PortSwigger. Accessed 2022-04-28.
  31. Rashid, Fahmida Y. 2011. "MySQL.com Hacked by SQL Injection Attack." Blog, eWeek, TechnologyAdvice, March 28. Accessed 2022-03-24.
  32. Sadeghian, Amirmohammad, Mazdak Zamani, and Suhaimi Ibrahim. 2013. "SQL Injection is Still Alive: A Study on SQL Injection Signature Evasion Techniques." IEEE International Conference on Informatics and Creative Multimedia, pp. 265-268, Sept 4-6. doi: 10.1109/ICICM.2013.52. Accessed 2022-01-18.
  33. Su, Zhendong, and Gary Wassermann. 2006. "The Essence of Command Injection Attacks in Web Applications." Association for Computing Machinery (ACM) SIGPLAN Notices, vol. 41, no. 1, pp. 372–382, January 11. doi: 10.1145/1111037.1111070. Accessed 2022-03-23.
  34. UC Berkeley. 2022. "How to Protect Against SQL Injection Attacks." Berkeley Information Security Office, UC Berkeley. Accessed 2022-04-29.
  35. Vermeer, Brian. 2021. "SQL injection cheat sheet: 8 best practices to prevent SQL injection attacks." Blog, Synk, March 26. Accessed 2022-04-29.
  36. Yan, Lu, Xiaohong Li, Ruitao Feng, Zhiyong Feng, and Jing Hu. 2014. "Detection Method of the Second-Order SQL Injection in Web Applications." In: Liu, S., Duan, Z. (eds), Structured Object-Oriented Formal Language and Method, SOFL+MSVL 2013, Lecture Notes in Computer Science, vol. 8332. Springer, Cham. doi: 10.1007/978-3-319-04915-1_11. Accessed 2022-04-28.
  37. Zhu, Alex, and Wei Qi Yan. 2017. "Exploring Defense of SQL Injection Attack in Penetration Testing." International Journal of Digital Crime and Forensics, vol. 9, no. 4, pp. 62-71, October. doi: 10.4018/IJDCF.2017100106. Accessed 2022-04-06.
  38. xkcd. 2007. "Exploits of a Mom." xkcd, October 10. Accessed 2022-02-28.

Further Reading

  1. Tajpour, Atefeh, Mohammad Zaman Heydari, Maslin Masrom, and Suhaimi Ibrahim. 2010. "SQL injection detection and prevention tools assessment." IEEE 3rd International Conference on Computer Science and Information Technology, pp. 518-522, July 9-11. doi: 10.1109/ICCSIT.2010.5563777. Accessed 2022-03-16.
  2. Rawat, Romil and Shailendra Kumar Shrivastav. 2012. "SQL injection attack Detection using SVM." International Journal of Computer Applications, vol. 42, no.13, pp. 1-4, March. doi: 10.5120/5749-7043. Accessed 2022-03-17.
  3. Joshi, Anamika, and Geetha V. 2014. "SQL Injection detection using machine learning." IEEE International Conference on Control, Instrumentation, Communication and Computational Technologies (ICCICCT), pp. 1111-1115, July 10-11. doi: 10.1109/ICCICCT.2014.6993127.Accessed 2022-03-19.
  4. Huang, Yao-Wen, Shih-Kun Huang, Tsung-Po Lin, and Chung-Hung Tsai. 2003. "Web Application Security Assessment by Fault Injection and Behavior Monitoring." WWW '03: Proceedings of the 12th international conference on World Wide Web, pp. 148-159, May 20. doi: 10.1145/775152. Accessed 2022-04-06.
  5. Ping, Chen. 2017. "A second-order SQL injection detection method." IEEE 2nd Information Technology, Networking, Electronic and Automation Control Conference (ITNEC), pp., December 15-17. doi: 10.1109/ITNEC.2017.8285104. Accessed 2022-04-06.
  6. Vittie, Lori Mac. 2007. "SQL Injection Evasion Detection." White paper, F5 Networks, September. Accessed 2022-04-03.

Article Stats

Author-wise Stats for Article Edits

Author
No. of Edits
No. of Chats
DevCoins
5
5
2205
6
0
2062
2229
Words
2
Likes
5277
Hits

Cite As

Devopedia. 2022. "SQL Injection." Version 11, April 30. Accessed 2023-11-12. https://devopedia.org/sql-injection
Contributed by
2 authors


Last updated on
2022-04-30 04:20:51
  • Blind SQL Injection
  • Second-Order SQL Injection
  • Web Exploitation
  • Injection Attacks
  • Database Management System
  • Structured Query Language