In everyday life web technology is omnipresent and applications are increasingly being developed directly as web and cloud services. The dominance of web services continues to grow, whether it is in the shape of browser-operated controls, IoT devices, web apps or online services. As a result, web security has also become an important issue. Leading the list of attacks is SQL injection. In this article you will learn what this is all about, how to recognise such an attack and, above all, how to protect yourself.
In Switzerland alone, there are on average 447 web-based cyber-attacks every single day. These include what are known as "injection attacks" – the number 1 in the OWASP top 10 vulnerabilities of 2020. The tricky thing about these attacks is that potentially, they can be used to access internal company systems that are not otherwise exposed to the internet. This is done by exploiting a web server's communication with, for example, system resources, third-party interfaces or a database.
One of these kinds of attack is what is known as "SQL injection". In an SQL injection, commands are injected into an application's communication in order to communicate indirectly with the database management system – and it is done with malicious intent. SQL (Structured Query Language) is used to communicate with databases. Using commands, the data contained in the database can be interrogated, managed and edited.
From the theory into practice – this is how an SQL injection attack works
11.44 pm: Pascal, the IT manager at a web design start-up, suddenly wakes, startled. On his bedside table his mobile phone is vibrating. Is there a family emergency? The name displayed is "Stefan Sulzer", one of Pascal's colleagues from work – a real workaholic who is probably still working on a new major client's front page design. So luckily, it is just an IT problem!
Indeed, that is just what it is. Stefan explains that something is wrong with the project server. Requests are taking longer than normal, and some page content is not loading at all. Pascal needs to take a quick look at it, despite how late it is. However, he suspects that there is only a nightly maintenance script running. To check, Pascal calls up the prototype of the client's home page. At the beginning of the week, the server with the test page was exposed to the internet and a subdomain was set up to preview the test server. The test page was still built on the start-up's old framework, as not all the features desired are currently available on the new version. After a short time, the test page appears, but with no text elements, as well as an error message:
Warning: mysql_connect(): Access denied for user 'admin_frontpage'@ '172.18.10.33' (using password: YES) in C:\xampp\htdocs\dorpando_crm\dbCon.php on line 35
That is strange: is the database server offline? Pascal logs into the company VPN to access the database server directly. His admin account login works. The database is therefore accessible and authorisation management is also working. Fortunately, the log of the SQL commands was activated on the development server – it looks like this:
210107 23:32:15 12864 Connect firstname.lastname@example.org as anonymous on
12864 Init DB pagecontent_client
12864 Query SELECT * FROM product AS p INNER JOIN offer AS o ON p.id = o.pid WHERE o.id BETWEEN 81 AND 86 UNION ALL SELECT CONCAT(0x7162707a71,IFNULL(CAST(table_name AS NCHAR),0x20),0x71626a6a71) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN (0x73716c696578616d706c65)-- -
Pascal quickly realises what the problem is. It is an attack, not a malfunction! The attackers seem to have succeeded in manipulating the server's SQL commands through a user input on the test page. Based on the log, Pascal recognises that the structure of the database has been read out. Using the same method, the attackers were also able to read and delete the contents and change authorisations.
Now Pascal is wide awake. There are so many questions running through his head. Is this pure vandalism or have the attackers maybe been able to extract the database including the user information and password hashes before they deleted it? Have other potential vulnerabilities been discovered?
Is it possible to stop an SQL injection?
All the same, Pascal tries to remain calm. For the time being, the forensic analysis will have to wait. Now it is a case of taking immediate measures to limit the damage. What is the best course of action? Pascal is considering using a back-up to restore the affected database and putting it into maintenance mode so that read-only access is possible. This would ensure that the page is displayed correctly again. However, there is a risk that the attacker will pull more data from the instance or find other databases on the same server.
To prevent further SQL injection, what are called "prepared statements" ought to be used in the application's code. These allow the database itself to distinguish between manual user input and SQL queries, so that user input is deliberately not interpreted. This stops any changes being made to the database request. However, it would take longer to implement these prepared statements, which is why Pascal decides to use a firewall rule to separate the server from the Internet for the time being, especially since it is "only" a demonstration version.
This leaves him with some uncertainty. Are there other security vulnerabilities in the company's own web framework? Who are the attackers and how far have they got? Have other websites created by the start-up that also use the same framework also been affected? For Pascal, it is going to be a long night...
Why web penetration tests and security monitoring are worthwhile
Although this example is a made up one, the reality is that attempts to penetrate web services are constantly happening. What can we learn from incidents like this? Bots developed by attackers are constantly scouring the internet for vulnerable applications. This is precisely why monitoring and predefined defence measures are important, but as is so often true, prevention is better than cure. Vulnerabilities should be detected and fixed at the source, within the application itself.
To specifically protect against web vulnerabilities such as SQL injection and many other web security risks, InfoGuard offers the following web application audits, including:
- Web Application Audit
The web application audit is the standard test for web services and websites. This test checks a web application for security vulnerabilities and wrong configurations using the OWASP guidelines, as well as running extra checks that have been developed by InfoGuard. Authentication specifications such as OAuth or SAML are also analysed in detail and examined for any vulnerabilities.
- Web API Security Audit
This audit focuses specifically on a web-based API interface. REST or SOAP based APIs are not only checked from the input validation or business logic side, but also for API specific vulnerabilities such as "mass assignment", where more than the mandatory attributes of objects can be written on the server. These tests are based on the OWASP API Security Project.
- Web Application Audit mit Source Code Review
As well as the web application audit, the application source code is also reviewed. This allows a penetration tester to detect vulnerabilities that are already in the source code and verify them. This process is also referred to as "white box" testing.
- OWASP Top 10 Web Application Security Audit
The top 10 audit is an abridged web application audit that focuses on the OWASP Top 10 vulnerabilities, which are adapted and published based on current findings in the software industry. Reviewing these potential vulnerabilities is an effective way of promoting security understanding and culture in software development.
As you can see, there are lots of different ways to protect yourself against SQL injection attacks. Do you know what the vulnerabilities of your web applications are? Contact us! Working closely with you, we will identify which web penetration test and audit best suits your needs.