loading...
Cover image for Understanding SQL Injection and Prevention using Parameter Binding in PHP

Understanding SQL Injection and Prevention using Parameter Binding in PHP

mukherjee96 profile image Aritra Mukherjee Originally published at geekyminds.co.in on ・1 min read

Happy Diwali 🎆 folks! When I learnt PHP for the first time, I wasn't really aware of the good practices regarding web security in PHP. Over the years, fuelled by my inquisitiveness and will for perfection, I put the effort in hours of research to figure out how exactly we can safely interact with the database in PHP.

If you try to do so yourself, you will realise that the experience of traversing the PHP documentation is not that convenient and there exist multiple ways of doing the same thing which is frankly very confusing to beginners. 🙄

Therefore, in this article, I decided to compile what I learnt and show you two ways of preventing most SQL Injection attacks in PHP (MySQLi and PDO) in a comparative fashion and explain their usage step by step. 🎉

This is what I am going to cover:

  • SQL Injection
    • Short explanation
    • A simple example
  • Preventing SQL Injection
    • Using Parameter Binding in MySQLi
    • Using Parameter Binding in PDO
  • MySQLi vs PDO

In my next article, I am going to dive deep into Parameter Binding with real-life examples.

Continue reading "Preventing SQL Injection with PHP using Parameter Binding"

The post Preventing SQL Injection with PHP using Parameter Binding appeared first on GeekyMinds.

Posted on Oct 27 '19 by:

mukherjee96 profile

Aritra Mukherjee

@mukherjee96

Co-founder of GeekyMinds. Passionate about cross-platform app development using web technologies! 🌐

Discussion

markdown guide
 

Parameterisation can help but in itself is not a total solution.

In my first PHP project it was entirely safe to do this:

"SELECT columns FROM table WHERE x = $x AND y = '$y' AND z = '" . db_escape($z) . "'";

The reason for how this could be safe is because I validated all input to know exactly what I was getting starting with allowing nothing first then allowing only what was necessary.

I also made sure I understood what went on everywhere.

My first mistake was to use magic quotes and it didn't take me long to realise that was a mistake. Because I had mastered escaping only when needed I could already see it escaping when not needed and even not escaping when needed such as the user agent string. I abandoned blind faith very quickly.

I feel people's approach to parameterised queries is sometimes the same as magic quotes. It wont address all security concerns.

To master security it is not possible to have faith. Instead everything must be understood.

You can easily experiment with this. Write a codebase like mine living on the edge that writes queries like that but is 100% safe. See how many people raise SQL injection issues then ask them all to exploit the issue.

Many will be surprised when they can't. Their jaws will drop. They will still have a hard time coming to terms that there is nothing wrong with the code.

Something interesting happens when you do the reverse. Make code that is unsafe but has all the markers of being safe. Ask people to review it independently and you'll get some coming back saying its safe because CRSF is turned on in the framework and its using parameterised queries. See their astonishment when it can be shown to be unsafe. The problem here are not the people who get it right and spot the real problem but those who get it wrong.

For people as of yet unable to write secure code a safety net might be better than nothing but it's neither infallible, cost free nor a replacement for learning to code more effectively.

If people don't learn to strictly validate input they will eventually get caught out when there's a case the fallbacks don't cover. It is not something you have to learn to do but also understand.

Safety nets can raise the level from 0 to 13 but not all the way up to 15. They'll close a bunch of hatches but wont seal the deck.

Parameterisation has conveniences beyond security. There have been cases without input validation it still fails on things like character sets for SQL injection. Regardless, it is far harder to SQL inject with parameterisation and impossible if configured properly and implemented properly.

But keep in mind SQL injection is not the only thing that could go wrong. Indirect object access is now taking the web by storm.

 

You're correct. Achieving perfection in terms of web security is a utopian dream. We can spend time and seal off each entry one by one by escaping them manually, but imagine the time that would take for large projects. I think we should rather use frameworks than write core PHP. Popular frameworks like Laravel and Django (Python) will have teams updating the framework's ORM layer to make sure it can handle the latest threats.

 

That might be a bit more complicated.

If you carefully examine those who can make things very secure, either 100% or as close to it as reasonable achievable (it's never good to say 100% in security) there are certain tricks where it's more than being about whether or not someone is willing to go as far as possible down the path of diminishing returns.

They might appear more involved at first but make things easier. Allow a few known good things then deny everything else is one example of that. Many people take the wrong turn down the maze of block a few bad things then allow everything else where security cost does balloon out of control. Frameworks are themselves notorious for this mistake. If you want to have a laugh, take a look at Code Igniter's XSS Clean.

Having good input validation also makes things much easier. Sadly for even modern frameworks this area isn't very mature. It also requires some care in teaching people. Many people also validate or sanitise for output on input which is another wrong turn down the maze.

Once you've been down the maze and worked out the paths of least resistance for achieving security then that does grant certain hints that can be shared to help others in their journey.

Framework writers can constantly work to fix their own blunders in their code but they cannot solve the biggest threat of all, their users. It's not possible to fool proof everything.

Indeed, for productivity there are times where you might have to take the risk though it should not be done naively. Eventually that risk has to be picked up. It's important to retain a risk register or a document containing sacrifices that should later be addressed.

If were talking about things such as sacrificing security for cutting costs or deferring responsibility for security to a third party this is something that needs to be balanced very very carefully.

I tend to say never say never because there are sometimes circumstances where you have to take risks to have a chance to achieve an objective at all but those risks also need to be acknowledged.

I'd like to say honest is always the best policy but it doesn't work well in selling software or yourself. Then I don't know what you can do because your competitors aren't going to be honest and they're going to win the market through their dishonesty or presenting their product as nothing other than perfection itself.

The nature of the business is such that situations arise where you must often choose between putting bread on the table or going with your conscience.

ORM really doesn't do much for security.

I believe parameterisation can do though if applied right. Even for a developer that can do without it, it can reduce the cognitive load if applied right but not to zero. It still means there are things left over to check. When you add good input validation into the mix, then naturally proper output formatting leading on from that (which parameterisation is a subset of) then you end up covering most bases.

You will eventually reach a point where you start to find limits with frameworks. Many do not handle transactions very well for example, I always end up having to build my own system for managing transactions.

It does of course also depends on how high your stakes are or if costs can be covered elsewhere. Some might deem it cheaper to have support to cover when things go wrong rather than to have transactions so that things do not go wrong.

In some cases there's a legal or contractual obligation to maximise security. It can often be very hard to be deserving of such contracts but to get them. One party will claim they're secure yet instead cut costs such that they can't make that guarantee. Another party will offer the honest price and the honest guarantees but not get the contract.