[PHP] SQL injection blocks?

Hi,
Well, Let me start off with the reason why im here asking about this. I run an online game, Which was only ever intended to be a place to learn php but people joined and it turned into a game. Anyways, Earlier today someone managed to inject sql queries somewhere in the coding and dropped half the user table. It created no errors in the error log files or the error log tab in the cpanel so i cant trace it. I have gave up on tracing exactly where and would rather just go over the whole site again and secure every page.

The problem im facing is, I know nothing about SQL injections, so its relatively hard to even know where to start with respect to blocking them. As the hacking moto goes, Something like "You cant fully break something you dont fully understand" and the same applied here kinda, Cant fix what i dont know how to use.

So the question is pretty much this, What steps can i take to prevent this?

I have googled alot on it and most of the info is just complicated as hell and doesnt help me much at all. Then there is the other results from the search that are from companies wanting to do the work for you, Which i dont want, I learn nothing from letting someone else do the work and i want to learn, Not rely totally on other people forever.


Thanks in advance for any help regarding this,
Luke.

I am not a sql person, use it for what I need but nothing else, but most of my friends do use it and have told me that most of the time exploits happened because of a addon that has a exploit or the actual sql and needs updating. I don't think you can really protect yourself from a exploit 100% because your bound to have a hole somewhere but then again I don't work with sql much, so I could be wrong.

You're wrong :wink:

There is a very easy, save and secure way to pretect yourself from SQL injections. Ofcourse, it depends on the database engine you're using.

If you use MySQL, there's mysql_real_escape_string():

PHP Code:

---

$variable = mysql_real_escape_string($unsecured_variable, $database_instance); 


---

If you do NOT use MySQL its a tad bit more difficult but still not impossible, you just have to use htmlentities() with the optional ENT_QUOTES parameter:

PHP Code:

---

$variable = htmlentities($unsecured_variable, ENT_QUOTES); 


---

Note that if you use htmlentities you must use html_entity_decode to translate it back for display on the web.

Also, if there is userinput that you display straigt on the web it is a very, very smart thing to use strip_tags() to make sure they don't use XSS exploits or other malicious javascript on your site.

Note that you must secure all user input. Not just formdata, but GET parameters as well. Every variable you ever use in an SQL query must be secure for your defense to be perfect.

Lastly, to see where they injected you, try to find the time of the attack and then search trough the logs of your webserver - Apache or IIS most likely. There will be most likely a request like GET index.php?variable='";DROP%20TABLE&20tablename;'

Thank you so much FragFrog, Having it all in one post like that makes it so much easier for me to understand instead of having alot of useless junk on a page for a google search.

From what you posted i not have alot of work to do, 200+ pages to scour through and edit to use these functions, Hopefully this is all i need and dont mess any of it up :biggrin2:

Frag can you show me a example of mysql injection that could hack a account on a website like http://www.thishabbo.net usersystem?

I'd have to read the source to give you a short answer to that Zzl.

enOtsoL: if you're going to update your queries anyway, may I suggest you start using a database class as well? Will save you a lot of trouble later on.. :wink:

I might write a guide / release mine somewhere in the near future.. :icon6:

I havent got to learning about classes yet, What would be the pros and cons of using classes in this?

I may take that route if i know how it would help :)

Using classes and generally making your code object oriented makes it easier to read and maintain. Removes a lot of the clutter trough interfaces & factories and enables you to quickly update core functionality.

Another smart move would be to allow the username controlling the databse only the powers that it needs, e.g. only 'select' instead of all privileges, this means that users trying to inject will only be able to select as far as I am aware, but don't quote me on that.

You are correct Mario_Party. Furthermore, by creating a seperate user for each project and limiting its rights you make sure someone can not possibly read data from other databases or modify them - this is a standard security practice available on all major commercial database engines.

Note that for any dynamic features to be available you'll need to give your user update rights as well :wink:

Use prepared statements in all sensitive areas (areas where user input is used to do something to a database, if its a static query or one that isnt directly dependent on user input, its not sensitive) to stop all sql injection, fools.

What most people dont realise is that there are a ton of ways of doing sql injection, first theres the regular ' which you think can be stopped by replacing ' with '' until you find out that then if they use \' on some dbms that will bypass it causing \'' to be a ' at the end and then the close apostrophe. Past this there are several more intelligent ways of doing sql injection, including using unicode conversion to pass something that isnt a ' but gets converted to it. Prepared statements will stop all of these because the data is directly sent to the query engine rather than through the parser. If you use

select * from memb_info where username = ?
set param 1 to ' or '' = ' (fake the attack) it will actually look for people with a username of ' or '' = '

Prepared statements are available in ADO (for asp websites and other applications) php (at least in mysql php, probably also in the mssql extension) java, C# and probably every language you use that can talk to a database.

Whitepaper - Adv SQL Injection - FileFront.com

Just a starting point for someone looking to read up on the basics.

Quote:

---

Originally Posted by ghell

[nonsense]

---

You're talking about parametrized queries, and no, you do NOT need them with PHP and MySQL because with mysql_real_escape_string you don't have to escape anything in your data.

Parametrized queries are usefull, yes, but they're a lot more trouble to modify, duplicate and create. Also, older versions of MySQL for instance don't even support them.

MSSQL does ever since MSSQL 7 (so for the past 10 years or so), but its rarely used in combination with PHP - and with htmlentities you do not NEED to escape them, you simply transform them into htmlentities - which is 100% secure. If you really think replacing ' with " is the only way to avoid SQL injection you are sorely mistaken, and probably don't know anything about PHP's functionality in this aspect.