(This is part 1 of a series on web security; see part 2.)
What’s wrong with this code?
Any jokester who says “it looks fine to me” will be sent to the spice mines of Kessel. But I think for observant readers, a couple of critical security errors will practically jump off the screen:
- User inputs are being concatenated directly into a SQL query string, risking a SQL injection attack.
- Passwords are stored in plaintext, exposing users to further harm in the event the database is accessed.
In the Real World
If you keep up with the news, you may have seen that no less an internet giant than Yahoo may have been guilty of both of the above mistakes, leading to a breach of 435K user credentials. This is a disaster for any company that safeguards private user data.
You can’t depend on users to protect themselves; somewhere out in the world today is a person who set up the same password on a Justin Bieber mailing list website and also their online banking. So if even a fluffy, non-important website drops the ball, users may see their bank accounts emptied. That’s probably an extreme worst case, though there is a whole range of other mischief you don’t want to enable either. The point is, your users trust you, and they don’t know what you are doing with their data. Be worthy of their trust.
Get In the Mindset
A technique I find helpful for programmers is to temporarily put down their shining knight helmet and try on a black hat for size. Put yourself in a creative trouble-maker frame of mind. Ask the question: how could somebody compromise my website and harm my users or my organization?
In the case of the code snippet at the top of this article, it is trivially easy for a hacker to enter a user name or password that will disrupt the query. By placing complex SQL expressions (perhaps cleverly including SQL comments) in the user name and password inputs, you can easily delete or insert records. What would totally frustrate your hacking attempts, though, is if the programmer used their framework’s feature for formal query value parameters (see for C#, Python, etc). And that, of course, is the correct answer here. Never concatenate user inputs into SQL strings. It’s even a bad idea to write your own sanitizing functions, because you’ll probably get a detail wrong, even if you’re a smart person. Just use the framework, all the time, or else use a mainstream ORM that hides the query string safely out of your code’s sight.