SQL injection attacks are a very popular web hacking technique. Known as SQLi, these attacks occur when one or more valid SQL statements are \u201cinjected\u201d into an input field to be processed by an underlying database. <\/p>\n\n\n\n
Structured Query Language (SQL) is a command-and-control language for relational databases.<\/p>\n\n\n\n
An attack occurs when an application accepts data from unreliable sources \u2014 data that has been modified to be interpreted as code \u2014 and does not perform a proper validation before using the data to perform a dynamic query on the database. <\/p>\n\n\n\n
When designing an application, it is important to keep in mind that any user-entered data can be modified arbitrarily.<\/p>\n\n\n\n
Attacks on web applications are usually performed by modifying parameters in the URL, using the HTTP GET<\/em> method or, as we will see in the examples below, modifying any type of data input with the HTTP POST<\/em> method. Depending on how the web application is designed, this vulnerability can be exploited either by modifying HTTP headers such as User Agent, cookies, referrer, or the system’s own headers.<\/p>\n\n\n\n Other potential attack vectors should also be considered, for example, bar code readers, QR readers, or video cameras with text recognition.<\/p>\n\n\n\n It is important to note that attackers may be either external or members of the affected organization.<\/p>\n\n\n\n Any unwanted action executed on a database as a result of an SQLi attack can affect one or more information security pillars. <\/p>\n\n\n\n A successful attack can allow the attacker to perform different actions that affect the confidentiality, integrity, and\/or availability of the information contained in the database.<\/p>\n\n\n\n Examples:<\/p>\n\n\n\n Other vulnerabilities that an SQLi attack might exploit include authentication failures and\/or the modification of a user’s profile authorization to perform certain actions on a resource. For example, in case of an attack against a database where user keys are stored, obfuscating the keys with robust cryptographic hashes would prevent unauthorized access to the keys and their potential use by an attacker.<\/p>\n\n\n\n For all the above, a successful SQLi attack simultaneously affects several different pillars of information security.<\/p>\n\n\n\n There are different types of measures that can be considered when a system needs to communicate with a relational database using SQL.<\/p>\n\n\n\n Some of the basic measures that should be implemented include:<\/p>\n\n\n\n Preparing SQL statements and storing them in a variable before their execution is a simple and secure way for developers to work. Doing this ahead of time ensures that an attacker will not be able to insert statements into our database, as shown in the second example below.<\/p>\n\n\n\n The most popular languages \u200b\u200bhave methods to securely parameterize statements requiring user-supplied input. These languages include:<\/p>\n\n\n\n When executing procedures directly on the database, care must be taken not to include the generation of unsecure dynamic SQL statements. These procedures must have their inputs validated and an adequate “escape” functionality.<\/p>\n\n\n\n User-supplied data must always be properly validated, not only to prevent SQL injections. <\/p>\n\n\n\n The use of dynamic variables for table or column names is not recommended, neither is specifying sort order (ASC or DESC). If necessary, prior validations should be used, such as converting the inputs to Boolean variables or using SWITCH, Sort of, or other functions. <\/p>\n\n\n\n Escaping user-supplied inputs to convert them to another format such as strings should be used with care, as this does not prevent every injection. The escape characters technique depends on each database engine, so it is important to implement further controls and validations to keep attackers from bypassing this measure.<\/p>\n\n\n\n Below are some examples of code that would enable SQLi attacks.<\/p>\n\n\n\n Example 1: Separating results into pages, using PHP and Postgres.<\/p>\n\n\n\n <?php<\/p>\n $\u00edndice = $argv[0]; <\/p>\n $consulta = \"SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $\u00edndice;\";<\/p>\n $resultado = pg_query($conexi\u00f3n, $consulta);<\/p> \n ?><\/p> \n <\/code>\n<\/pre>\n\n\n\n A user typically clicks the \u201cnext\u201d and \u201cback\u201d buttons to browse between results, which would change the decimal value of the \u201cindex\u201d variable in the URL. <\/p>\n\n\n\n This behavior would not cause any issues, but if a malicious agent were to decide to add the following code to the URL:<\/p>\n\n\n\n 0;<\/p>\n insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)<\/p>\n select 'crack', usesysid, 't','t','crack'<\/p>\n from pg_shadow where usename='postgres';<\/p>\n --<\/p><\/code><\/pre>\n\n\n\n the $consulta<\/em> variable would look like this:<\/p>\n\n\n\n $consulta = \"SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET 0;<\/p>\n insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)<\/p>\n select 'superusuario', usesysid, 't','t','password'<\/p>\n from pg_shadow where usename='postgres';<\/p>\n --;\"<\/p><\/code><\/pre>\n\n\n\n As a result, a \u201csuperuser\u201d user would be created with privileges, who would then be able to perform malicious activities such as those described earlier in this article.<\/p>\n\n\n\n One way to fix this problem would be to use PDOs (PHP Data Objects):<\/p>\n\n\n\n <?php<\/p> \n $stmt = $pdo->prepare(\"SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET :\u00edndice;\");<\/p>\n $\u00edndice = $argv[0]; <\/p>\n $stmt->bindParam(':indice', $indice);<\/p>\n $stmt->execute()<\/p>\n ?><\/p><\/code><\/pre>\n\n\n\n Example 2: Authentication bypass<\/p>\n\n\n\n Suppose a web application has an authentication form that accepts a username and password as inputs.<\/p>\n\n\n\n This form is being processed by code containing the following SQL statement:<\/p>\n\n\n\n consulta = \"SELECT * FROM users WHERE username = \"'\" + username + \"' AND password = '\" + password + \"'\"<\/p><\/code><\/pre>\n\n\n\n As we can see, the query to the database is built using SQL statements and the user-supplied values are assigned directly to the variables. <\/p>\n\n\n\n In this case, a malicious user could enter admin<\/em> as the user and pass’ OR ‘1’=’1<\/em> as the password.<\/p>\n\n\n\n The final query to the database would look like this:<\/p>\n\n\n\n consulta = SELECT * FROM users WHERE username = 'admin' AND (password = ' pass' OR '1'='1 ')<\/p><\/code><\/pre>\n\n\n\n Given that the Boolean condition will always be true, this query would fetch all the data associated with privileged user ‘admin<\/em>‘.<\/p>\n\n\n\n SQL injection attacks are preventable if the necessary controls are implemented during the application development phase.<\/p>\n\n\n\n Developer teams should have a procedure that includes best practices for secure software development, including a prudent time for the testing phase prior to going into production.<\/p>\n\n\n\n <\/p>\n\n\n\n Authors:<\/strong> Graciela Martinez, Guillermo Pereyra<\/p>\n\n\n\n Date: <\/strong>22\/03\/2022<\/p>\n\n\n\n https:\/\/blog.sucuri.net\/2022\/01\/understanding-website-sql-injections.html<\/a><\/p>\n https:\/\/owasp.org\/Top10\/A03_2021-Injection\/<\/a><\/p>\n https:\/\/cheatsheetseries.owasp.org\/cheatsheets\/SQL_Injection_Prevention_Cheat_Sheet.html<\/a><\/p>\n https:\/\/www.esecurityplanet.com\/threats\/how-to-prevent-sql-injection-attacks\/<\/a><\/p>\n https:\/\/www.php.net\/manual\/es\/security.database.sql-injection.php<\/a><\/p>\nWhat are the possible consequences of an SQL injection attack?<\/strong><\/h2>\n\n\n\n
How to prevent an SQL injection attack<\/strong><\/h2>\n\n\n\n
Use of parameterized queries: prepared statements<\/strong><\/h3>\n\n\n\n
Use of procedures: stored procedures<\/strong><\/h3>\n\n\n\n
Validation of user-supplied data<\/strong><\/h3>\n\n\n\n
Escaping all authorized user entries: escape characters<\/strong><\/h3>\n\n\n\n
Examples<\/strong><\/h2>\n\n\n\n
\n Conclusion<\/strong><\/h2>\n\n\n\n
References:<\/strong><\/h2>\n\n\n