A while back, I made a post talking about a Linkedin password dump that let hackers gain access to Mark Zuckerberg’s twitter account. We don’t know how it is that hackers got their hands on the millions of Linkedin passwords, but it got me thinking of a certain type of attack that is much more common than it should be, and one that you should take care of avoiding if you run a web application. This attack is commonly known as an SQL injection (often pronounced “sequel injection”), and it uses something as harmless as your search bar to access the web application’s database. 

SQL stands for Structured Query Language and is essentially how most programs talk to databases. It was developed in the prehistoric era of web-computing of 1974 and is actually very good at its job despite its age. It’s a very simple language to use and works very well with different other languages, such as PHP. PHP is an incredibly powerful and intuitive programming language, but unfortunately not the safest. It is one of the most widely used programming languages for web application, with Facebook, Yahoo, and Wikipedia all being at one point coded in it, however, it does not come with any built-in security, and hackers can easily access web applications that are badly built to get information you definitely would not want them to have.

Here’s how it works. A typical SQL query looks something like this:
Let me explain what it is I just typed out. Let’s pretend that I own a badly made online store for craft supplies. If I wanted to see the store’s selection of popsicle sticks, I would use its search bar to search for popsicle sticks, and it would give me a result of the name of the item (popsicle stick), the price of the item (12.99$), and the quantity of the item (500). I type in the search query that PHP sends to the site’s database as an SQL query where it selects and item (I don’t know what, hence the question mark) from a table, (again, don’t know what table this will be) where the column (another mystery) is like my query. Hence, I get a result. I can also type in a term like “opsicle stic” to get the result I want which means that there are wildcards on the side making the SQL query look something like this:
As a hacker who doesn’t have access to the back-end code, I can only manipulate what’s in the quotation marks. So, what would happen if I entered just a quotation mark as my search query? If the website isn’t coded against it, the SQL query will look something like this:
The program assumes the quotation we entered as the query is the ending quotation of the query, and doesn’t know what to do with the last three symbols. This will return an error, even though a product in a craft supplies shop can have a quotation mark in its name, for example, if it was named something like Burt’s Pipe Cleaners. Here’s how a hacker can abuse it. If you add a semicolon, and then a comment sign (two dashes in MySQL, for example) to indicate a comment in the query, it becomes:
SELECT ? FROM ? WHERE ? LIKE ‘%‘;— %’;
Which will ask the database for simply a wildcard, meaning everything. A hacker now can input commands into PHP by using this trick (which, again, can be prevented relatively easily) to access sensitive info. Here’s an example, let’s say the hacker wanted to have a timer on the response a website gives out. If they were trying to access a site that they know uses MySQL, for example, they can make a query of
popsicle stick’ AND 1 = SLEEP(2);– 
to make an SQL query of

SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘AND 1 = SLEEP(2);— %’;
which will make the server wait two seconds for every query found.

Hopefully, this helped you understand just how easy an SQL injection really can be. Just remember that hackers don’t usually go after big targets, it’s much easier to catch a smaller fish that doesn’t care about it’s web app security as much, which could be you.

I recently wrote a post explaining in brief about the dangers of SQL injections. I ended the post explaining how it was possible to run commands through a search bar in order to access and control a web application’s database. I decided that it would be better if I went a little more in-depth and wrote out what a typical SQL injection attack can look like against an unprotected database.

So, as I wrote in the previous post, let’s pretend that I am a proud owner of a craft supplies store, for arts-and-crafts and fun little projects to do with the kids. My craft store uses PHP and MySQL to access its database of items. In my previous post, I explained the basic way SQL injections are run. Essentially, adding a comment in the right place will allow a hacker to secretly hide commands for PHP to carry out. If you aren’t sure how it works, the basics are all there.

I gave an example of a command to make the database wait a certain amount of seconds before giving a response, which sounds pretty useless. Why waste time making your own hacking process slower?  The reason behind it is there is a valid form of SQL injection called a “blind injection”, where one would use SQL injection commands without expecting to get any visible output. What I mean is, for example, to test what kind of RDBMS (relational database management systemsoftware the web app uses. MySQL has a sleep function called SLEEP(), another software may use something like waitForDelay(). By testing out the different functions between different software, a hacker can get a better understanding of what they’re dealing with. Another thing someone could do would be to ask to receive data from a table that they aren’t sure exists, along with a delay. If there is a delay but no output, the hacker will know that the table exists, but the output is hidden somehow.

Let’s say a hacker has happened on my website and wants to steal the passwords that I have stored on it. They can show those passwords by what is called a union. A union is the sandwiching of two tables one under the other, provided they have the same number of columns. The hacker assumes when they type in to search for “popsicle sticks” that the table outputted has three columns, one for the name, price, and quantity of the item. They can test this hypothesis by entering the SQL query
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT 1,2,3 FROM dual);— %’;
where the UNION function should output the numbers 1, 2, and 3 as separate columns from the “dual” table. The “dual” table is a blank hypothetical table used for testing in MySQL, which the hacker knows is the software used thanks to the previous injection.

If the result is the numbers 1, 2, and 3 printed out successfully at the bottom of the table, the hacker’s in business. They now know that they can output data to the table, which is world-endingly bad for the creator of the website (me). MySQL has an information schema called information_schema.tables that contains information about the different tables in the program. The hacker can access this by making the SQL query
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT TABLE_NAME, TABLE_SCHEMA, 3 FROM information_schema.tables);— %’;
which will output the table name, what kind of table it is, and the number 3, because the number of columns has to be the same always.

The hacker will scroll through the many different table names and find a table that I created called “users”, which is where I could keep all of my different user data, maybe, we’ll see (yes, of course, don’t you worry). The hacker doesn’t want to simply union this new table onto the current one because there may be a different number of columns than 3. They would instead write an SQL query of something like this
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT COLUMN_NAME, 2, 3 FROM information_schema.columns WHERE TABLE_NAME = ‘users’);— %’;
which will output the different column names from that table.

From the columns, the hacker will find a column named “userPasswords”, which they can output with an SQL query, and then dump onto the internet.

This was just one way in which a hacker can use SQL injections to gain access to personal information on unprotected sites. It’s quite a bit like cracking a puzzle, and just like doing puzzles, along with most other intellectual things, a robot can most likely do it better. This is why, as a side note, always remember to have some sort of spam protection, so that your website isn’t bombarded with thousands of SQL hack attempts.

  • Cross-Site Scripting (“XSS“)
    • Hackers can inject JavaScript into a web page
    • Used to steal cookies a session data
    • Often very successful just because browser trusts JavaScript
      • Protection:
        • Sanitize any dynamic context that gets output to browser (HTML, JavaScript, JSON, XML…)
        • Pay special attention to data that come directly from URLs or forms
        • Be careful about database data, cookies, session data
        • Use Whitelists to allow certain HTML tags and sanitize everything else
    • Cross-Site Request Forgery (CSRF)
      • Hackers tricks user into making a request to your server
      • Used for fraudulent clicks
      • Forging login request
        • Protection
          • Accept POST request only
          • Use a “form token” in user’s section
          • Add a hidden field to forms with form token as value
          • Compare session form token and submitted form token
          • Store the token generation time in user’s session
    • SQL Injection
      • Hacker is able to execute arbitrary SQL request in order to probe database schema, steal data(usernames, passwords, credit cards, encrypted data), assign elevated privileges, truncate or drop tables
        • Protection
          • Use limited privileges to application’s database user
          • Sanitize input
          • Escape for SQL using libraries
          • Use prepared statements
    • URL manipulation
      • Editing the URL string to probe the site
      • Can be used for revealing private information, performing restricted actions
        • Protection
      • Remember that URLs are exposed and easily editable
      • Implement proper access control
      • Keep error messages vague
      • Clarify your GET and POST requests, only POST requests should be used for making changes
    • Cookie Stealing
      • Cookie data is visible to users
      • Cookies can be stolen using XSS attack
      • Remember that cookies can be sniffed by observing network traffic by using packet analyzers (most popular Wireshark)
        • Protection
          • Put only non sensitive data in cookies
          • Use HttpOnly cookies
        • Use HTTPs cookies
        • Set cookie expiration date
        • Set cookie domain, sub domain and path
        • Encrypt cookie data
        • User server side sessions instead of client side cookies
    • Session hijacking
      • Stealing session ID is similar to stealing cookie but much more valuable
      • Can be used to steal personal info, passwords
      • Often done by network sniffing
      • Never use open wireless networks at coffee shops for transmitting sensitive data
      • Variation of session hijacking is session fixation
      • Session fixation is opposite to session hijacking, it trick a user into a hacker provided session identifier
        • Protection
          • Use SSL
          • Save user agent in session and confirm it (not ideal method)
          • Check IP address of a computer who is making a request (not ideal as well)
          • Use HttpOnly cookies
          • Regenerate session ID periodically, at key points, especially important to regenerate after log in
          • Expire and remove old session files regularly and keep track of last activity in session
        • Do not accept session identifier from from GET or POST variables, session identifier should come from only one place – cookies
    • Remote system execution attack

      • It’s the most dangerous attack when hacker remotely run operating system commands on a web server
        • Protection
          • Avoid system execution keywords (they are language specific)
          • Perform system execution with extra caution
          • Sanitize any dynamic data carefully
          • Understand system commands and their syntax
          • Add additional data validation
    • File upload abuse
      • Can be used to upload too much data (quantity, file size)
      • Can be used to upload warm or virus
        • Protection
          • Require user authentication, no anonymous uploads
          • Limit maximum upload size
          • Limit allowable file formats, file extensions
          • Use caution when opening uploaded file
          • Do not host uploaded files which have not been verified
    • Denial of Service (DoS) attack

      • Attempt to make a server unavailable to users
      • Usually performed by overloading a server with requests
      • Includes DNS and routing disruption
      • If performed by distributed network pf computers it called DDoS
        • Protection
          • Properly configure firewalls, IDS, switches, load balancers and routers
        • Collection of reverse proxies
        • Map you infrastructure
        • Keep infrastructure up to date
        • Make network traffic visible
        • Develop DRP plan
        • Consider changing IP address
        • “Black hole” or “null route” traffic