Project introduction
We (a Group of five people, including me) worked on an mvp for our Venture idea during a semester course “ITS Venture”.
Our idea is to provide very short versions of ToS (Terms of Service) for various websites, starting with the german market. The mvp is a website, which consists of mainly one page, the search page. Usersare presented a search bar, just as they would find it on Google. They can search for a URL or name of a Website and our website returns a small version of that website’s ToS plus a notice of how much time they saved by reading the short version of the ToS.
This result is not supposed to summarize the entire ToS of a website, but the most important parts, like return policies or giving the user information they usually wouldn’t expect. That information can be positive or negative. A positive example would be, when a company is seated in your country.
Project architecture
Our Website is hosted on a webspace hoster. They offer their customers MySQL Databases and run on PHP 8.0. This will be important for later.
We decided to use PHP, since we weren’t fully aware, that this was only a webspace and not a full server. Later on we will find that PHP was a good choice. The website uses javascript to communicate with a php script on the server to pull data from the database.
Needed security feature
Our website has been built without SQL Injections in mind since it is an mvp first and foremost. It is in our best interest to make our website secure against SQL injections.
SQL injections are a technique where the attackers try to insert SQL queries into input fields on a website in hope that those queries make it to the database to be processed. Such a weakness can be heavily abused when it allows normal users to query statements on the database.
Why focus on this attack technique?
SQL injections are a popular technique according to several sources.
In a research report for “Web Attacks and Gaming Abuse” from US-based cloud service provider Akamai they noted:
“The growth of SQLi as an attack vector over the last two years should concern website owners. In the first quarter of 2017, SQLi accounted for 44% of application layer attacks. This actually represented a rather large drop from the previous baseline, which was historically slightly over 50%.”
This is coming from the US gaming industry, but showcases that SQL injections are still a popular attack method.
As soon as some information about the database are present, the attacker can easily test for vulnerabilities on a given website.
In case of PHP, when we use a query like so:
<?php$searchQuery = “SELECT * FROM websites WHERE name = ‘%”. $_POST[‘searchterm’] .”%”;?>
If an attacker was to insert something like the following:
amazon’ ; DROP TABLE websites —
The SQL query the database would receive would look something like this: SELECT * FROM websites WHERE name = ‘amazon’ ; DROP TABLE websites –
Double dashes to comment out everything after the attacking query and the ‘; to successfully end the search query itself. After that the command to drop the table called websites is inserted to delete the table from the database.
This would leave our website without the main database table for as long as it takes us to resotre it from a backup.
Imagine something like this on a database with user data and user logins. The attacker might be able to circumvent the password altogether, gaining access to a users data by knowing only their username.
The tech DD
For the use of PHP we have several possible solutions that can eliminate the vulnerability on our website.
Let’s take a look at some of them:
Management rights, Input validation, parameterized queries, escaping, stored procedures and a web application firewall (WAF).
Not giving attackers too much information about our database table names and so on should be obvious and therefore applies to all the following techniques. For SQL injections the attacker usually needs some knowledge of the targeted database.
Management rights:
We should never use the database’s superuser account to connect to it via a script. The script itself should only have the necessary rights to alter the tables. For example, it should never be necessary for user accessible webpages to have the rights to drop entire tables on a database.
Input validation:
Validate if the input from the user is allowed. This can be done by running regular expressions over the query before sending it to the database or a fixed set of options offered to the user. In short, you have to make sure the input sent by the user has the correct type, length and format.
Prepared statements / Parameterized queries:
Pre-compiling an SQL-statement so it can be fed parameters.
The input data from the users is quoted and will therefor not cause any issues.
In PHP we can use PDO with parameterized queries to prevent SQL injections.
See: PHP Manual on PDO prepare
“Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information. Also, calling PDO::prepare() and PDOStatement::execute() helps to prevent SQL injection attacks by eliminating the need to manually quote and escape the parameters.”
Escaping:
Character escaping functions can be used to escape user input. Using such a function will avoid characters that could lead to unintended SQL commands.
PHP mysqli offers a function real_escape_string to escape characters of a given variable.
Stored procedures:
Stored procedures can be used to access the contents of a database, keeping the access abstract.
“The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.”
WAF:
A web application firewall can be operate in front of the web server to monitor the traffic which goes in and out. This traffic can be scanned for malicious patterns. You can customize the rules the WAF is operating on and therefor control what is being thrown away and what is allowed to pass to your web server. A WAF offers easy deployment of new policies.
A WAF can provide protection from:
· SQL injection
· Cross-site scripting
· Session hacking
· DDoS attacks
· Cookie poisoning
· Parameter tampering
The best solution in detail
For us if we use PHP PDO, escaping is not explicitly needed. Deploying a WAF and altering it’s policies is not an option, at least not with our current webspace hoster. A WAF has a lot of benefits so it will surely be on our radar for the future. With the given time it is never a bad idea to stack multiple solutions to a problem, but for now using prepared statements with PHP PDO is a great way to secure our code against malicious user inputs. They are quick and easy to implement and can also be built upon with different solution techniques.
Introducing the theory
Since we can easily change the PHP code, parameterized queries are something we can actually use.
To change this, we would only have to go into the code and change a few lines of code, as you will see in the next section. The PHP manual site for PDO prepare describes it as follows:
“Prepares an SQL statement to be executed by the PDOStatement::execute() method. The statement template can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. Both named and question mark parameter markers cannot be used within the same statement template; only one or the other parameter style. Use these parameters to bind any user-input, do not include the user-input directly in the query.“
Everything in this can be applied to our code, since our backend code is not doing a whole lot.
Our products main work is in summarizing the ToS of websites, which makes the technical side a lot easier to deal with.
On top of that we should use limited access database accounts and not superusers in our PHP scripts to keep the attackers possibilities at a minimum from the start.
PHP offers validation and sanatization filters for data.
“Validation is used to validate or check if the data meets certain qualifications. For example, passing in FILTER_VALIDATE_EMAIL will determine if the data is a valid email address, but will not change the data itself.” (php.net Introduction to filters)
“Sanitization will sanitize the data, so it may alter it by removing undesired characters. For example, passing in FILTER_SANITIZE_EMAIL will remove characters that are inappropriate for an email address to contain. That said, it does not validate the data.” (php.net Introduction to filters)
Functions like this make dealing with user input a lot easier and fast, since we can use proven to work solutions instead of building our own checks that might still have flaws in them.
Applying the theory to our problem
If we take our example from before and modify it to use PDO it will look like this.
<?php$searchTerm = $_POST[‘searchterm’];$dbConnection = new PDO('mysql:host=localhost;dbname=example', 'dbuser', 'dbpasswd');$dbCconnection ->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);// using PDO prepare and bindParam to parameterize the query$searchQuery = “SELECT * FROM websites WHERE name = :sitename ”;$query = $dbConnection->prepare($searchQuery);$query -> bindParam(‘:sitename’, $searchTerm);?>
The function prepare is sending the sql statement to the database server which then parses it separately from the parameters. By binding the parameters to the placeholders the parameters, the malicious user input will never be parsed as a statement but appears only as a string to be used for the search.
To reiterate with the words of very nice stack overflow contributors:
“The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend.”
“Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).”
(Community owned top rated answer on stackoverflow)
To really make sure PDO behaves in the way we want it to we should set the PDO attribute ATTR_EMULATE_PREPARES to false. This tells PDO to use real prepared statements and not parse them before sending them to the database server.
This combined with validating and sanatizing the input data, will make our code and database way less vulnerable.
Conclusion
As we learned SQL Injections can have a considerable impact on a database or even user data that should be secure and not be seen by anyone else but the user itself.
PHP was a good choice to take, thanks to it’s already existing security functions and it’s large community. You should also consider how large a community surrounding the tech you want to choose actually is. A big community and a well documented programming language can make it easier for you to secure your data.
With PHP we have multiple options for security measures. Since for us the webspace hoster is a consideration too, the easiest and fastest solution which is also seen as reliable in the industry is to use parameterized queries with PHP PDO prepare. On top of that having a solid account management and giving only access to functions needed will reduce the risks taken before even applying any measures in code.
Prepared statements can not prevent everything. An attacker might try to use wildcard characters (*, %, ? and so on) which might still work under certain circumstances, for example when we use the operator LIKE . Prepared statements also do not secure use from attackers using malicious javascript code. That’s why a combination of security techniques is always advised.
The discussed security measures are not specific to PHP. Other programming languages have their own libraries or built in functions to tackle this security issue.
So regardless of what platform might be used, the theory applies and I see prepared statements as a necessity, if nothing else is done to prevent SQL injections, unless you really want to live risky.
Used Sources
Comic/Image:
https://www.wordfence.com/learn/how-to-prevent-sql-injection-attacks/
Informatonal:
State of the Internet / Security | Web Attacks and Gaming Abuse (Volume 5, Issue 3) | Akamai
https://www.php.net/manual/de/security.database.sql-injection.php
https://www.php.net/manual/en/intro.filter.php
https://www.ptsecurity.com/ww-en/analytics/knowledge-base/how-to-prevent-sql-injection-attacks/
https://www.cbronline.com/news/sql-injection-attacks
https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
