Was talking to my friend about a site he is working on that had been attacked by SQL injection. Its something that makes you think hes not an idiot, I better recheck I’m not creating sites that are vulnerable to attacks. If the client is annoyed if some idiot can put his weird video up over their homepage thats nothing to how embarrassed the developer of that site feels.
What is a SQL Injection attack
A SQL injection attack is where someone manages to inject SQL into a database query on your site in order to alter that query so they can modify the database for your site. They can then go on to inject undesirable data, delete your data or expose data you don’t want exposed.
What to do to stop it
There are a number of steps you can take to reduce the likely hood of a SQL injection attack happening to you. If your using ready made software such as WordPress or another CMS the manufacturers should have dealt with these vulnerabilities. That is one of the best reasons to use ready made software but you should keep it up to date.
- Check input, filter it and escape it
- Set the database up securely – so that that user can only access the information they need to and has the lowest amount of privileges they need
- Parameterize your queries – so even with weird inputs the intent of the query can’t be changed.
- Don’t display useful error information – to a user, but do log it for you to analyze later.
- Encrypt sensitive data – even if someone gets into the database they won’t be able to use it.
- Know your database – SQL injection can be pretty clever, and different databases have different vulnerabilities. SQL SERVER for example has some quite esoteric features that can do some pretty weird stuff, you need to be aware of that if your going to use it.
You can hire specialists to do penetration testing1 which may be worthwhile depending on your budget and the value of your data.
Its a great idea to review your database backup regime. The morning you wake up and find your database has been compromised is bound to be the day when you find there isn’t a backup to restore to.
1) They do insist on calling themselves ‘pen testers’ which just makes me laugh.
Been working on some flexible style sheets for someone else so I thought I should do some for me so I’ve started on the homepage of this site (its the one most people look at). As ever its a work in progress I have not done this blog yet.
This is the homepage at 900px wide:
This is the homepage at 300px and 500px wide:
People are using a wide range of devices of different screen sizes and resolutions. You want your content to look good on all those devices don’t you?. Even if its only for legibility. Designing a site to only work on a huge great desktop monitor will please the art director when he prints it out but it isn’t going to please a customer who is using a 1060px wide screen or even a customer with his browser window set as a small portion of a larger screen.
I’ve been using media queries support does vary somewhat across desktop devices. The most notable one I found didn’t work for me was Internet Explorer 8. You can get around that one if you include a js script. You can get an idea of the support offered by various browsers on When can I use.
What sizes? devices
It depends on your circumstances, what kind of devices are people using to visit your site with. If you have a load of visitors using massive art director style monitors you might want create a very big screen set of styles. If lots of people are using small screen devices then you need to look at those. If only a very small number of people use that very odd dimension then it might not be worth bothering.
This is reasonably easy you can resize your browser window and see how you site adapts in your various browsers. Opera is good for this as they have a simple tick box that allows you to turn on ‘show webpage dimensions in titlebar’ which gives you a simple way to see the current depth and height as you resize. On a Mac its under Opera > Preferences > Advanced > Browsing.
You can check your site in emulators as starting point but support does vary from device to device and ideally you want to test on real devices. They have settings that alter how they display your content that might be set to zoom out a larger site or ignore the media queries you have made completely.
Ideally you should do this stuff when your putting a site together to start with and consider it when designing the site. Some things are awkward to scale, I’ve found tables awkward in the past.
You can bash an existing site into a more flexible approach or just the most used parts of the site.
Been assessing the vulnerabilities of a site I’m building at the moment and trying to come up with a list of potential vulnerabilities so that I can check that I’ve dealt with them all. This is where I’ve got to so far please point out any you think I’ve missed.
- errors aren’t displayed, other than we just had an error if really necesary, not the details
- errors are logged
- if logged to files those files aren’t accessible by visitors
Only public viewable files are visible
- access to any non public folders are restricted
- includes, class definitions settings files etc.
- if you were to try and access they would act like a resource that doesn’t exist
- input isn’t trusted
- input is filtered, escaped etc
- output is escaped
- query params are quoted, bound dealt with according to your database
- the security of access to the database itself
- database users
- database server access
Vulnerabilities of software included
Any ready made systems, libraries used in the site are at the latest version and they don’t publish what version they are at. Including language and server software versions e.g. say ruby don’t say ruby 1.9
Cross site scripting
Origin of requests
- where origin of requests should be via self make sure it is.
- login points throttle repeated requests in order to slow down dictionary attacks
- log repeated login attempts by ip and useragent
- block abusers according to blocking policy
- passwords are non dictionary words and of adequate length and variety of case and character
- passwords are stored encrypted in a way that makes unencrypting them awkward if they were ever accessed.
- all public forms have some form of captcha device
- all public email addresses are obscured in some way
File system access
- ftp, sftp, ssh users
- file permissions are correct
Access with curl and more bot like access paths mirror that of normal browser based viewer or are more restricted.
- unsuccessful logins and successful logins are logged.
Monitoring of access and file modification times to check files havn’t been modified when they shouldn’t have been by some other than authorised source.
Well that is my first attempt I guess I can always add to it as I think of new issues and read more. Hopefully without getting too paranoid.
I spend quite a lot of time reading other peoples blogs. Here are some of the recent posts I’ve been reading which I found interesting or useful. Some of these might have been around for while before I came across them but they are new to me.
- If php had a flux capicitor – Distilled wiseness from Matt, but no pictures of DeLoreans or Doc Brown :).
- The MicroPHP Manifesto – An interesting idea I can see sense in it.
- Grumpy Programmer – opiniated, but some useful stuff in there. He also does a podcast with Ed Finkler at dev/hell
- Lorna Jane did a series of posts on how she builds a RESTful PHP Server at Understanding the request, Routing the request and Output handlers.
- Derick Rethans on spatial indexes from open street map. You too can find out where the nearest pubs to you are (or more useful stuff).
- Roger Johansson is always worth reading at 456bereastreet.com about web development with a special focus on accessibly. I found this one visited link styling, about what css styles you can use to signify visited links taking into account recent browser privacy settings, useful.