Wednesday, March 19, 2008

SQL injection and bad programming practice

I live in a town that is the nemesis of e-commerce applications. The name of my town is Quartu Sant'Elena
Notice that the name contains an apostrophe, which for all practical purposes is represented by a single quote.
Single quotes have a bad reputation, because they may be a symptom of SQL injection. Whenever I enter the name of my town in a web form to buy something, I hold my breath, because I dread what comes next.
The smartest applications have a Ajax interface with online completion, and take the name of the town without problems. The less advanced ones show a multiple choice list containing my town name.
The bad ones refuse the name of the town as invalid, and force me to enter an alternate spelling (Quartu S.Elena), which is recognized by most Italians as being equivalent.
The very bad ones, after forcing me to change the name of the town, refuse my credit card as invalid. The reason? The billing address of my credit card does not match with the one I entered in the web form.
The very terrible ones check the credit card billing address some days after the transaction was completed. I bought a domain name from a big registrar company. They accepted the credit card and assigned me the domain, which I started using immediately. Three days later, I got a message saying that my credit card charge was not being accepted. The domain was blocked, so I could not register it with another company, and the company did not solve my problem, despite hours of phone calls. All for a lazy programming practice!
Real SQL injection prevention is achieved by input checking and string escaping, not by blindly rejecting everything that looks like a quote.

1 comment:

Roland Bouman said...

Similar fun is seen nowadays by mechanisms to twart XSS injection. Many, many sites are vulnerable to transient XSS injection (50% is really not an exaggeration); some are vulnerable to persistent injection.

From the ones that are not vulnerable, only a minorty gets the escaping right. Many PHP sites use "addslashes" (that's what I suspect at least) and stubbornly return the added slashes. Many others simply filter out the less-than and greater-than signs, and a lot of sites return the offending characters with "double escaping" - so,
" is returned as "

C'mon people, it can't be that hard?! You're all making the web a bad place to be in...

Vote on Planet MySQL