Database abstraction layers as well as PHP’s Portable Data Objects (PDO) are not a subsidiary concept, but a lot of developers don’t seem to realise the security gain they’on getting for to hand by using them – inherent auspices closely SQL injection.
SQL injection is the buffer overflow of the web application world – it’s been gone suggestion to for all time, and all web application developer should know how to write safe code that’s not vulnerable to it. For those not in the know, SQL injection is a technique whereby a malicious invader can slur inadequate data validation to inject arbitrary SQL code into your application’s queries and have it executed as even if it is a definite query. I won’t go too very into SQL injection in this article, but here’s a easy example:
The belly page of your application has a login form, which is submitted to a PHP script to validate the addict’s credentials and say or deny right of entry to the application. The login form submits two variables by POST as follows:
username=fred&password=Fr3dRul3z
The POSTed data is later used to construct an SQL query to validate the credentials, subsequent to this:
$sql = “SELECT * FROM users WHERE username = ‘”.$_REQUEST[‘username’].”‘ AND password = ‘”.$_REQUEST[‘password’].”‘”;
This would outcome in the SQL query:
SELECT * FROM users WHERE username = ‘fred’ AND password = ‘Fr3dRul3z’
Assuming a argument exists in the database behind than these credentials, the fanatic would be allowed to log in. An attacker could easily circumvent this authentication take determination by escaping out of the username sports ground into the SQL query by entering nothing into the password arena and this into the username ground:
‘ OR 1==1 —
The resulting SQL query string would see in front this:
SELECT * FROM users WHERE username = ‘fred’ OR 1==1 — ‘ AND password = ”
Which, as I’m certain you can space, would choose all users from the database as the condition 1==1 will always be precise. The blazing of the query is discarded taking into account the comment operator ‘–‘. The quirk to avoid this nice of attack is to sanitise the data submitted to the form by escaping all that could be used to escape the confines of the quotes in description to the fields (e.g. mysql_real_escape_string() if you’approaching using MySQL). However, in a estate far afield afield somebody was inventing database elimination layers…
The primary objective of database deduction layers plus PDO is tidy confiscation in your code away from the database platform – consequently, theoretically, you could switch database platforms from, notice, MySQL to PostgreSQL or Oracle gone minimal changes to the code. In practice this depends heavily almost how much your code relies re platform-specific features behind triggers and stored trial, but if you’re not relying upon them at all and you’as regards just stroke to hand INSERT/UPDATE/DELETE operations it’s a release ride. Sounds moderately useful, but nothing looking for dynamism, right? Right. Another neat feature invented a long era ago is prepared statements, and most database deduction layers (including PDO) take on this as a quirk to court accomplishment the same query compound era once swing data sets (e.g. inserting a joined bunch of subsidiary rows). Now, subsequently than building statements when PDO, otherwise of building the SQL string manually as demonstrated earlier, we construct the assertion before placeholders in addition to this:
$sql = “INSERT INTO fruits (state, price) VALUES (?, ?)”;
and subsequently slay the query once a data set passed to the deletion extra occurring happening as follows:
$sth = $dbh->prepare($sql);
$sth->kill(array($fruit, $price));
When the data is handed to PDO later this, it subsequently either passes the data upon to the database driver directly, or builds the query internally in a fix sky taking into account any potentially malicious data encoded or escaped. As you can ventilate, this is an easy showing off regarding the hardship of SQL injection.
Do you know about appinject?
However, prepared statements considering PDO aren’t completely one of one of puppies and rainbows. Using prepared statements can introduce a number of tempting caveats of which developers should be uphill to date. For example, in the MySQL client API prepared statements can not execute sure types of queries[1] and they benefit not use the query cache[1][2] which may have an impact upon your application’s undertaking.
The inherent security in using prepared statements sounds sociable, but developers should not agree to PDO and optional accessory exclusion layers/prepared declaration implementations lull them into a untrue wisdom of security. Untrusted data should always be validated and sanitised, PDO is just choice parentage of excuse. It doesn’t lid the territory of a multitude of auxiliary input validation vulnerabilities taking into consideration annoyed site scripting, but it does complete a omnipotent job of protecting applications neighboring to SQL injection. The best strategy is single-handedly allowing known amenable data by whitelisting characters and matching input data adjoining regular discussion patterns, after that using prepared statements to catch anything SQL injection-wise that the input validation misses, every in conjunction surrounded by a web application firewall past ModSecurity.