[PHP / SQL] SQL Injection Blocker

[combatking0]

I have attempted to describe an SQL injection attack prevention mechanism for PHP enabled websites, but the forum interprets it as an SQL injection attack, so I have included the original post as a TXT file.

Does it look like something useful, as part of a wider range of security measures?

[Jim]

That'll work, I guess.  Maybe ban '-' as well?

What would also be good is for php programmers to tidy up their data access. e.g. this SMF forum code has sql data access scattered all through the code, it would be better if there was a data access layer where all the sql access is in one place.  That makes it easier to put sql injection measures in place.  And as a nice consequence makes it a lot easier to make schema changes.

There's also the parameterised query stuff in php that's supposed to be able to help stop sql injection.

Jim

[combatking0]

The script can be modified to ban just about any character you want, so '-' can be easily added.

Sometimes inverted commas / apostrophe's may be wanted, so I'll update it to produce SQL friendly inverted commas, which don't break the code.

I'll have to look up parameterised queries.

[Jim]

Check this out
http://us2.php.net/manual/en/pdo.prepared-statements.php

Jim

[Shockwave]

I was going to mention prepared statements but it seems like Jim beat me to it.
They are very simple to use, the query is sent to the server in two parts and they are very difficult to circumvent.

Another thing, you could use mod_security to prevent bad stuff being injected into your websites.

[benny!]

I can really recommend the Zend Framework for all general PHP development. It's really worth having a look at if you want to work on bigger PHP projects.

[combatking0]

I'll read up on these and see how to incorporate them into the function.

(edit) Without knowing what is installed on the webserver, for example, if the space is being rented from a hosting company, the presence of the Zend Framework and mod_security cannot always be guarenteed, but I'll see about looking for a host with these features, as I'm coding a shopping website.

I have attached an improved version of my noSQL script. The function now takes 3 arguments - the first is the string to be processed, and the others are boolean values.
The function always escapes slashes, inverted commas and double inverted commas.

If the second argument is true, the function removes semi-colons, asterisks, double minus signs (SQL comment delimeters) and those strange angled inverted commas used for enclosing column names and table names.

If the third argument is true, the function removes certain SQL keywords. Since it uses str_ireplace, it only works with PHP5 or later.

[Jim]

If you are going to be writing a shopping site of your own, you need to find a good framework that takes care of all this stuff for you.  Why not find an off the shelf package you can configure - there are lots of free ones?
MySql has some built-in functions for cleaning up sql
http://php.net/manual/en/function.mysql-escape-string.php

Jim

[combatking0]

Apparently, that function has been depreciated in favour of http://www.php.net/manual/en/function.mysql-real-escape-string.php

I'll see what overlap there is between this function and mine, and adjust accordingly.

I'd like to avoid using an available system if possible, so as to protect the inner workings of the site from hackers, although looking at the code of a pre-built system may give me more ideas for functionality and security.

[Jim]

The big advantage of using someone else's code is that you don't have to re-invent the wheel, and you hope that they will have thought of these security problems and have been baked in the fire of really being live on the internet.

If you build your own, likely the only security you have will be obscurity - the hackers won't hack you initially because you have code they don't understand.  But unless you are a php ninja the certainty is that you will make a mistake, or miss something and you will leave yourself wide open.

The importance of this security depends on how important this all is to you and your customers.  If you are not backing up your database or you are handling credit card numbers and other secrets then you have a lot of responsibility.

Please don't let me discourage you   Is it just you yourself coding this?

Jim

[benny!]

What is the project about ? Is it just an educational protoype shop or are you
really coding a shop for a customer ? If so, I really have to second Jim. Please be
careful about it. There are a lot of legal issues you need to think of. Just as a little
side note ... of course I do not want to discourage you to code ;-)

All the best!

[combatking0]

It's for my father-in-law.

The shop payments will be going through paypal, so there won't be any issues with credit card details or direct connections to bank accounts.

My Barcode Battler shop is crude, and has limited security, but its obscurity has probably saved it from attack. It has tought me a lot, and I will update it to my new system when it is ready.

It's just me, but I reckon my coding and problem solving skills should be enough to destroy most of the bugs before it goes live. Just in case they are not enough, there will be "Lock-down" buttons for the admins and customers which disable various accounts and features (depending on who is using them) pending repairs and upgrades.

The test machine may be very different to the live machine, but I'll keep a close eye on the error logs and set up a reminder for the site owner to back-up the database at a convinient frequency.