php + mysql security question

wasn't sure if this is more correct here or web security, so kindly excuse me if this is the wrong forum.

I have been given the task of creating a set of tools for a server that allows users to make more secure php+mysql webapps.

One of the modules is for database interconnect and my question is this:

can an attacker force a different type of char encoding into a mysql query? For example unicode or even hex (does mysql support embedded sql?)? It seems that mysql can deal with unicode, according to the docs, but it is unclear if it can deal with multiple encoding types within a single query or set of queries.

If any of these are true, will using a different encoding type than the php engine is running bypass mysql_escape_string() only to be decoded at the db level? I have been able to find no data on this type of attack. This could mean either it isn't possible and i am just being paranoid or it is possible and has not occurred to anyone else yet.

The current method I am using is to break down the db to four accounts with limited permissions:

user_select(select)
user_insert(insert)
user_delete(delete, select)
user_update(update, select)

preg_match("/^delete.*delete|select|0x/i", $query)

is an example of what I am currently using to control the delete queries. I only want one instance of delete, zero instances of select and zero instances of 0x until i am sure about the encoding issue.

Does anyone have any thoughts about this subject matter? I'd rather not do lots of testing if this is a known issue/non-issue and I have just been blind.

thanks,

catch

Perhaps I'm misunderstanding your question, but I think at least part of your question can be answered by looking in the php docs.

The functions urldecode and perhaps utf_decode will solve at least some of your problems. From reading the docs, it seems that these two functions will help filter out things such as semicolons and other control characters that a user attempted to slip through. By converting them back to ascii, it's easier to filter them correctly. I know this doesn't answer your larger question of whether these encodings make php/mysql vulnerable, but perhaps it will at least make your code less succeptible to these types of attack.

As for MySQL's handling of embedded SQL, I haven't had any experience in that area, so I can't help. Sorry.

A good question

Quote:

---

If any of these are true, will using a different encoding type than the php engine is running bypass mysql_escape_string() only to be decoded at the db level? I have been able to find no data on this type of attack. This could mean either it isn't possible and i am just being paranoid or it is possible and has not occurred to anyone else yet.

---

I have the same thinking as you, which is why I will often strip charactors such as % as well as ' " = & ; *.

However who knows what the furture holds, which way I have recently stoppped thinking about what to strip, and rather what is allowed. For example if you are expecting a number, then only allow 0-9 for that input in the php code. If it is an address then only a-z A-Z 0-9 and space. This may mean that you have a few problem espially with some Irish name (thank you slarty for point that out) eg Mc'donald. But but by giving proper explaination to the user what a cirtain character is not allowed.

Therefore I dont think it is best to write a single function to deal with the input validation to prevent various kinds of attacks including SQL injection, XSS etc. As each variable should be delt with on an individual bases.

Have a look at www.owasp.org they have some very good information regarding this matter

Hope this helps

SittingDuck