SQL injection Cheat Sheet, Study notes of Programming Languages

Download SQL injection Cheat Sheet and more Study notes Programming Languages in PDF only on Docsity! A S S U M P T I O N S You are a developer or you know programming You have limited web application security knowledge You need to know how SQL injection attacks happen You need to know how to fix SQL injection issues in your code • • • • In this cheat sheet, we will assume that: SQL Injection Cheat Sheet FOR DEVELOPERS S Q L I N J E C T I O N F A Q What SQL servers are affected by SQL injections? All SQL servers may be affected by SQL injections: MySQL, MSSQL, Oracle, PostgreSQL, and more. What programming languages are affected by SQL injections? SQL injections may happen in any programming language. What may be the consequences of an SQL injection? An SQL injection may lead to data leaks but it may also lead to complete system compromise. How common are SQL injections? In 2020, SQL injections were found by Acunetix on average in 7% of web apps. Do web application firewalls (WAF) protect against SQL injections? No, WAFs only make it more difficult for the attacker to send SQL injection payloads. • • • • • A P P E N D I X : A D D I T I O N A L R E S O U R C E S Acunetix: A general introduction to SQL injections Acunetix: A more detailed explanation of SQL injection types Acunetix: A more detailed explanation of blind SQL injections Acunetix: A detailed explanation of out-of-band SQL injections Acunetix: A practical example that shows how an SQL injection may lead to system compromise Acunetix: A practical example that shows how to analyze logs to discover an SQL injection attack Acunetix: A detailed article about preventing SQL injections in PHP Acunetix: An article about preventing SQL injections in Java Acunetix: An article about preventing blind SQL injections Pentestmonkey: Detailed SQL injection cheat sheets for penetration testers Bobby Tables: The most comprehensible library of SQL injection defense techniques for many programming languages Read more: acunetix.com/blog We keep your web applications secure G O A L S How do malicious hackers conduct SQL injection attacks How to fix your code that has SQL injection vulnerabilities How to avoid SQL injection vulnerabilities for the future • • • In this cheat sheet, you will learn: S Q L I N J E C T I O N T Y P E S T Y P E 1 : I N - B A N D S Q L I N J E C T I O N : E R R O R - B A S E D S Q L I N J E C T I O N The attacker sends a request designed to cause an error in the database server The server returns an error message to the attacker The attacker uses information contained in the error to escalate the attack This type of SQL injection is used to access sensitive information such as database type, file names, and more • • • • Payload: Result: The web application displays the following error in the browser: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74 • • E X A M P L E : T Y P E 2 : I N - B A N D S Q L I N J E C T I O N : U N I O N - B A S E D S Q L I N J E C T I O N The attacker uses a UNION clause in the payload The SQL engine combines sensitive information with legitimate information that the web application should display The web application displays sensitive information • • • Payload: Result: The web application displays the system version and the name of the current user: 8.0.22-0ubuntu0.20.04.2 acuart@localhost • • E X A M P L E : T Y P E 3 : B L I N D S Q L I N J E C T I O N : B O O L E A N - B A S E D S Q L I N J E C T I O N The attacker sends many payloads containing expressions that evaluate to either TRUE or FALSE Alternating between the two, the attacker can draw conclusions about the database and its contents This type of SQL injection is often used to access sensitive information when the web application returns neither meaningful error messages nor the targeted data itself • • • • • • E X A M P L E : T Y P E 4 : B L I N D S Q L I N J E C T I O N : T I M E - B A S E D S Q L I N J E C T I O N If the web application doesn’t return errors and the returned information is the same for boolean-based payloads, the attacker sends a payload that includes a time delay command such as SLEEP, which delays the whole response The attacker draws conclusions from the length of response delays and repeats the process as many times as necessary with different arguments This type of an SQL injection is often used to check whether any other SQL injections are possible This type of SQL injection may also, for example, be used to guess the content of a database cell a character at a time by using different ASCII values in conjunction with a time delay • • • • • • E X A M P L E : T Y P E 5 : O U T - O F - B A N D S Q L I N J E C T I O N • • E X A M P L E : This technique is available in many programming languages Instead of forming the query by using string concatenation, the query string includes parameters The prepared statements library replaces these parameters with values supplied by the user, so that SQL commands and user input (parameters) are passed separately • • • P A R A M E T E R I Z E D Q U E R I E S ( P R E P A R E D S T A T E M E N T S ) P H P E X A M P L E Using PHP Data Objects (PDO): J A V A E X A M P L E Use only if your programming language does not support prepared statements To avoid SQL injections, you must use prepared statements in stored procedures Available only for database engines that support stored procedures but most modern engines support them The query is prepared and stored in the database engine The application calls the stored procedure and passes variables to it • • • • • S T O R E D P R O C E D U R E S M Y S Q L E X A M P L E Creating the procedure: M S S Q L E X A M P L E Creating the procedure: SQL Injection DefenseP A R T 2 Note: To improve detection, it is best to employ several methods at the same time. However, if you cannot afford it, go for the most effective method first. M E T H O D T O O L S K E Y P R O S K E Y C O N S R A T I N G Manual code review Development environment May improve the general quality of code Unlikely to find SQL injections Manual penetration testing Attack proxies Able to find even very complex and rare types Very time and resource intensive Automatic code analysis (white box scanning) SAST software Can reach even code that is not used directly Reports a lot of false positives and does not prove that a vulnerability exists Automatic vulnerability scanning (black box scanning) DAST software Can run in any environment and at any development stage Does not point to the issue in the source code Automatic vulnerability scanning with proof of exploit and grey box sensors Acunetix Proves that the vulnerability exists by showing data that should be restricted; points to the error in source code or bytecode (PHP, Java, .NET, Node.js) SQL Injection DetectionP A R T 3 Your code uses unsanitized data from user input in SQL statements A malicious user includes SQL elements in the input in a tricky way Your code executes these SQL elements as part of legitimate SQL statements • • • SQL injections happen when: What Are SQL Injection AttacksP A R T 1 S I M P L E S Q L I N J E C T I O N E X A M P L E <?PHP $userid = $_GET["userid"]; $query = "SELECT user FROM users WHERE userid = $userid;"; $result = pg_query($conn, $query); ?> A T T A C K E R R E Q U E S T : Y O U R C O D E P R O C E S S E S T H E F O L L O W I N G S Q L Q U E R Y : $query = "SELECT user FROM users WHERE userid = 0; DELETE FROM users WHERE 1;"; As a result, if the current user (current database user) has suitable permissions, the entire users table is cleared. This type of SQL injection is possible only for some databases, for example, Microsoft SQL Server and Oracle The attacker includes a special database command in the payload – this command causes a request to an external resource (controlled by the attacker) The attacker monitors for attempts to contact the external resource, for example, DNS lookups or HTTP request logs of the external resource If there is a request coming once the payload is executed, this confirms that the SQL injection is possible The attacker accesses database information and can send it to the external resource • • • • • $dbh = new PDO('mysql:host=localhost;dbname=database', 'dbuser', 'dbpasswd'); $query = "SELECT column_name FROM table_name WHERE id = :id order by column_name desc"; $sth = $dbh->prepare($query); $sth->bindParam(':id', $_GET["id"]); $sth->execute(); $result = $sth->fetchColumn(); int id = Integer.parseInt(id); String query = "SELECT column_name FROM table_name WHERE id = ? order by column_name desc"; PreparedStatement stmt = connection.prepareStatement(query); stmt.setInt(1,id); ResultSet results = stmt.executeQuery(); CREATE PROCEDURE example(IN suppliedId VARCHAR(8)) BEGIN SELECT column_name FROM table_name WHERE id = suppliedId; END Calling the procedure with id = 1: CALL example("1"); SQL injection payload will not work: CALL example("0;DELETE FROM users WHERE 1"); CREATE PROCEDURE dbo.example @id nvarchar(8) AS SELECT column_name FROM table_name WHERE id = @id; GO Calling the procedure with id = 1: EXEC database.dbo.example 1; SQL injection payload will not work: EXEC database.dbo.example 0;DELETE FROM USERS WHERE 1 Y O U R C O D E I N P H P : https://www.acunetix.com/websitesecurity/sql-injection/ https://www.acun tix.com/websitesecurity/sql-injec ion2/ https://www.acunetix.com/websitesecurity/blind-sql-injection/ https://www.acunetix.com/blog/articles/blind-out-of-band-sql-injec ion-vulnerability-testing-added-acumo itor/ https://www.acunetix.com/bl g/articles/exploiti g-sql-injection-example/ https://www. unetix.com/blog/article /using-logs-to-inves igate-a-web-application-attack/ https://www.acunetix.com/blog/articles/prevent-sql-inj ction-vulnerabilities-in-php-applications/ https://www.acunetix.c m/how-to-prevent-sql-injections-j / https://www. cunetix.com/blog/articles/blind-sql-injection/ http://pentestmonkey.net/category/ch a -sheet/sql- njection https://bobby-tables.com/ https://www.acunetix.com/blo / https://www.acunetix.com/blog/articles/exploiting-sql-injection-example/ https://www.acunetix.com/white-papers/using-dast-to-get-the-most-out-of-wafs/ http://testphp.vulnweb.com/listproducts.php?cat=1%27 http://testphp.vulnweb.com/artists.php?artist=1%20AND%201=1 http://testphp.vulnweb.com/artists.php?artist=1%20AND%201=0 http://testphp.vulnweb.com/artists.php?%20artist=1-SLEEP(3) http://testphp.vulnweb.com/artists.php?artist=-1%20UNION%20SELECT%201,version(),current_user() https://www. cunetix.com/blog/web-security-zone/sql-injection-compromises-entire-country/ https://www.acunetix.com/acunetix-web-applic tion-vul erability-report/