This is the second post of a two-part series on PHP vulnerabilities. First of all, it is worth noting again that most of the vulnerabilities we will be discussing are not PHP specific. These vulnerabilities exist in most—if not all—programming languages used on the web, whether compiled or interpreted. I am just taking some time to focus on PHP vulnerabilities because of PHP’s continued popularity and the continued display of these attack types. One only needs to check out the web application section of exploitdb or any online database of exploits to see just how many of these vulnerabilities pop up on almost a day-to-day basis.
A little history; as many may already know, SQL injection was first brought into light on a mass scale in December 1998 on the legendary Phrack ezine by a security researcher named Jeff Forristal. Jeff is better known by his online pseudonym—which may ring some bells in those who are familiar with the history—”Rain Forest Puppy.” According to Jeff (Rain Forest Puppy), he came across the vulnerability one day while looking for security flaws in a Windows NT Server, which was popular at the time. In his own words, he ended up finding out that he could “change how SQL works” and essentially reveal sensitive database information by tweaking, or in some cases, piggybacking his own crafted SQL queries onto existing SQL queries embedded in code with actions destined for the backend database. And here we are, some 20 years later and little has changed. SQL injection is still a major headache to IT admins everywhere and its core cause, like command injection and possibly all injection vulnerabilities, is due to the lack of sanitization of user-controlled input.
Again, so much has been written about SQL injection attacks that it’s really hard to approach the subject from a new perspective. I can say that a lot of SQL injection (not all) in PHP-based web applications is the result of the UNION operator, (That’s use is defined as: “To combine the result from multiple SELECT statements into a single result set.”) which can be used to append or combine SQL queries to an existing statement. The UNION operator is available for use in most database syntaxes like MSSQL, Postgres, Oracle, etc. though the specific syntax may vary a little. I pulled our simplified code from an old capture-the-flag (CTF) I created for training purposes years ago and modified it for our use. The theme for the old CTF was to hack into a bad guy’s website who doxed or revealed sensitive info of a group that hacked his site in the past. The mission was to hack into his new, more secure site, capture a few flags along the way, get root access, then delete the dox info from the database and finally cover all your tracks. Our goal will be much simpler, of course.
There are many SQL injection tactics and data exfiltration techniques used for various attack situations. We have SQL subquery injection, blind SQL injection , out of band SQL injection, and all sorts of newer yet not that new tactics like using regex based SQL injection, extractvalue based SQL injections, blind SQL injection’s binary search techniques and bit shifting, etc.
In our example, we’re going to (again) keep it simple with a basic UNION based SQL injection followed up with a quick fix. Here is our sample code:
Let’s start with a quick breakdown of our PHP code. The PHP script above logs into the MySQL database (with root credentials which is very bad, but still happens too often), selects the vuln_db database it’s using for the current query, then passes the query to the backend MySQL database. As we can see, the parameter, sink, or user-controlled input in question is “id”, which is not sanitized at all. This means the script above just accepts whatever the PHP $_GET superglobal passes to it then places it into an already dynamic SQL query that pulls its resulting rows from the dox table based on the id number used. The lack of any sanitization and the use of a dynamic query, of course, leads to a serious SQL injection vulnerability. The vulnerability is made worse by the fact the MySQL root user is being used to make the query. This means any query executed by this app will be executed by the MySQL root user who will more than likely have read access to many files on the OS as well as potential write access to filesystem.
When appending the “id” parameter to the application path with a value of 25, we see it returns a row with a user whose name and address is returned by the database query to the application-as it should.
After reading the code we can see that this is a numeric-based injection as opposed to string-based injection due to the fact that a numeric id is what is needed to pull the row of data, so we can easily test for a vulnerability by appending a true statement to the current id (AND 1=1) followed by a false statement (AND 1=2) then examine the page for differences. If the false query (and 1=2) returns absolutely no SQL data in its response, but the true statement returns a healthy page there is a very good chance there is a SQL injection vulnerability on the website.
Now as stated, our example is an integer-based SQL injection meaning an integer was used (id=25) in the original query to retrieve a specific row of data. There are also string-based SQL injections too. If this was a string-based SQL injection, the test would be similar, but we would have to break out of the query by closing the first query with a single or double quote depending on how the original query was written. So our probe would be: ‘ and 1=1–+ and the false statement would be ‘ and 1=2–+, respectively. This may need to be repeated and checked with double quotes or: “ and 1=1–+ for a true query and “ and 1=2–+ for the false query. In some cases even: ’ or ‘a’=’a and ‘ or ‘a’=’b may work. The takeaway here is we are using programmatic or mathematic True and False SQL statements to test the backend query validation. It’s kind of common sense too, as 1 does equal 1 and 1 does not equal 2. And for string-based queries we can still use mathematic equations to check for SQL vulnerabilities. Just remember that we first must break out of the original query by closing it with a single or double quote.
So, let’s test our code for an integer-based SQL injection:
http://vulnserver.com/php/sql.php?id=25%20and%201=2 shows an empty page on a FALSE query.
However, http://vulserver.com/php/sql.php?id=25%20and%201=1 presents the page in its original form (Ugly, I know, but it will do):
Unsurprisingly, we have a SQL vulnerable web page. From here the attack is pretty trivial—assuming there is no web application firewall (WAF) or character filtering going on. Access to the original source code makes it even easier. Here’s how.
Reading the documentation, the UNION operator requires that the statement we craft match the SELECT statements in the original query. In a black box test we would have to acquire the column count using the “order by” statement, which can be used to count the existing number of columns in a query. If this isn’t available for whatever reason, we can just increment columns in our union select statement with integer or “null” values until we see an anomalous response. Because we have the original source code we can easily see how many columns there were in the query. What was that line again?
1 2 3 4 5 6
“select id, name, street, city, state, zip from dox where id=$id”;
The columns we see are “id, name, street, city, state, and zip” meaning there should ideally be 6 columns in each row of data meaning there will be 6 columns that need to be accounted for in our exploit code. Just to illustrate an order by statement, this is what the last column check would look like. If I were to request order by 7, the page would break.
A request checks for the existence of 6 columns:
A check for 7 columns gives us a broken page:
Now, the penetration tester or attacker just needs to craft a working exploit using his/her knowledge of how many columns are in the original query and God’s gift to penetration testers and hackers everywhere–information schema. Without going into too much detail, information_schema provides read-only access to any tables, columns, and procedures the current user has access to. So, there is an information_schema.tables and an information_schema.columns attackers can always use to find existing table and column names. This is true in blind SQL too. Long gone are the days of attackers having to guess tables or column names in major databases like MSSQL or MySQL. Even in blind scenarios where there is no output. Querying information_schema using the asci and substring functions can pull the characters reliably using True/False queries.
So now we have what we need to craft an important union query that will give us details about which column space holder we can use to query our data. Each integer is a placeholder for the columns. Our payload: /sql.php?Id=25 and 1=2 union select 1,2,3,4,5,6–
Notice we added a false statement (and 1=2) to negate the original query. This is how we break out of the original query. There are actually multiple ways this can be accomplished. We can feed it the false and 1=2 statement, we can also negate the integer 25 by adding a minus sign to it (id=-25) or just simply remove 25 altogether and replace it with null.
After we null or negate the original query, we follow it with a “union select” statement and finally a count of comma separated columns to represent each column of data in the original query followed by the SQL comment operator, which can be either two hyphens (sometimes followed by a + or space on string injections), a hash symbol, or a forward slash and wildcard (/*). The comment operator is used to end the query. This will comment out anything after our query that may exist in the backend code that could potentially break it. So, again our final piece of code here is:
Id=25 and 1=2 union select 1,2,3,4,5,6–
We actually have a lot to work with. We can inject code into columns 2,3,4,5, and 6. To get the current database name we queried the second column with database():
Now we can move onto to getting a list of tables by querying information_schema.tables for all of the tables in the current database() using something along the lines of the query below:
select table_name from information_schema.tables where table_schema=database()–
And from here we can query information_schema.columns for columns from a specific table. Users would be the logical choice for a penetration tester/attacker so the attacker could use credentials to log into the content management system and/or test the credentials against other potential login forms or network services. Our query to do this would be:
Select column_name from information_schema.columns where table_name=’users’–
And finally, we have all the information we need to craft our final exploit. We use 0x3a—which is a hexadecimal representation of a colon—as a delimiter so our data is formatted and not just one big mess of text.
Because the MySQL user is logged in as root (on purpose in this case), the attacker or penetration tester would also be able to read certain local system files and possibly even write a web shell for the operating system if the circumstances permit it. The database resides on the same server as the web server and the tester is able to map the full web server path to a writable directory. A full path disclosure vulnerability will usually aid in the discovery of the web server path, but it can also be found via brute force.
Using the MySQL load_file function we can read sensitive files like config files, which may hold database credentials. To gain code execution into outfile or the into dumpfile functions can be used to write a web shell.
We can write a web shell by selecting a simple piece of php shell code into a writeable directory like so:
id=-25 union select ‘’ into outfile ‘/var/www/html/vulnsite/php/lol.php’–
And our PHP code, written to lol.php in the same directory executes as expected.
The point of this little information dump is to show that not much knowledge is needed to execute a SQL injection attack. There are easy to follow, coherent steps with each different type of SQL injection attack that don’t require one to be a database administrator (DBA) or even to have a great understanding of SQL or programming. Through repetition, these techniques can become very easy for attackers so it’s good to be aware of how these attacks take place and how easy they are to perform. Not to mention, even the lazy people who don’t care to learn can use tools like SQLMap and Havij. In actuality, it only takes one small oversight and a kid with too much time on his hands to become victim to this type of attack that can cripple customer trust as well as bring all of the wrong kinds of attention.
Defending Against SQL Injection Attacks
As for mitigation, there are many ways to fix this type of SQL injection. Prepared statements and parameterized queries are often brought up as a mitigation tactic, and—while they are much better than dynamic queries and mitigate most SQL injection attacks—it cannot be stressed enough that user-controlled data must be sanitized regardless. Without sanitization, parameterized queries—if written incorrectly—can still lead to SQL injection vulnerabilities. In my opinion, it is best to be safe and use multiple mitigation strategies. Use prepared statements with parameterized queries, disable error_reporting, and sanitize all—I repeat ALL—user-controlled input.
Now, back to our example. For our over-simplified code example, PHP comes with a beautiful function called mysql_real_escape_string (PHP4-PHP5) and mysqli_real_escape_string (PHP5-PHP7) or for Object oriented code, mysqli::real_escape_string. Another option for our specific example would be to force our id values to only allow integers so we have a lot of options. I am using an old version of PHP and more of a basic procedural style of programming, so let’s keep it simple and create a simple function to clean any and all SQL data using mysql_real_escape_string. The quote placement within the function is very important and must be accounted for in this example. Simply wrapping mysql_real_escape_string around data that’s about to be passed to the database isn’t enough.
A function like this could be added to an include file and used in conjunction with parameterized queries to ensure your user-controlled data is always sanitized. Like I said, with this being an integer sd could also do something along the lines of $sql = int($sql);. Or like I stated in the previous post on command injection, we can always fall back on preg_match or preg_replace for a more granular protection strategy depending on the data you want vs. the malicious data you can expect. If you decide on regular expressions within preg_replace or preg_match, use a whitelist. There are simply too many character encodings and obfuscations to cover it all. Plus, it’s important not to reinvent the wheel here.
Well, this concludes this week’s PHP vulnerability. As we move through the next few weeks, I will probably bundle together a couple of vulnerabilities into one article—so we can move onto more recent topics related to vulnerabilities. Interesting things to come, I promise. Also, keep an eye out for an article on why you can’t depend solely on WAFs and filters. I will show you how determined attackers can and will evade these filters with real world examples.