Here's my situation: I have a website built with CakePHP and MySQL. My website has a public forum where members can post code samples. Some of these code samples are going to include PHP and MySQL code.
I understand that this is probably a dangerous thing to allow, but for one: Only members whom I have specifically "approved" may post, and two: I believe CakePHP already uses parameterized queries... and while I'm sure there are still other risks involved, that is not what my question is concerned with.
The problem:
In my local testing environment, I can submit posts that include SQL statements and strings with php code like <?php phpinfo(); ?>; but, when running the exact same code on my production server, I can't.
Rather than being redirected to /posts/index (which is what normally happens after posting), I am redirected back to the same page I was on before (/posts/edit/25, for example). It seems that when I attempt to submit form data that includes "illegal" substrings, the submittal silently fails, and CakePHP just sends me back to where I was before. (No flash messages on CakePHP's behalf.)
My diagnosis thus far:
CakePHP's /www/app/tmp/logs/error.log shows no sign of an error after I attempt to post this data. I don't have access to any other server log files at the moment.
I tried removing some of the php code from my posts. I was able to submit strings like <?php echo 'Hello World'; ?>, but if a function like phpinfo() was anywhere in the form data, the post would get "rejected". Stuffing my SQL examples with non-breaking-spaces also made it possible for me to submit form data that included SQL statements.
The MySQL user account that CakePHP uses is a little restricted. I forget exactly which actions it is allowed to perform, but I think they are just select, insert, update, delete, create, drop, and maybe a few others.
However, I also use phpMyAdmin locally to manage the production database, and when I access it via phpMyAdmin, I am using an admin account with maximum privileges. And when I manually change data through phpMyAdmin, strings like phpinfo(); in my code examples don't get rejected!
Even though this seemed like a small glimmer of hope, I have tested my site with CakePHP setup to use the MySQL admin user with max privileges, but I still can't submit my unsafe strings via the user-facing form.
My unsatisfying conclusion thus far:
Since the exact same php code behaves differently depending on my environment, I suspect this has something do with my PHP version, MySQL version, or some security measure my sysadmin has put in place.
My sysadmin is rather hard to get in touch with though, so I'm trying to figure what might be causing the problem on my own.
My question:
What might my problem be? Is there a PHP setting that prohibits "unsafe" strings, or some other commonly-known setting that I could request my sysadmin to change? Or perhaps might this be a MySQL setting? (Hard to tell given my phpMyAdmin's strange ability to do things that Cake can't.) Or how might I go about diagnosing this problem? (On my own; or, what kind of question I should ask my sysadmin?)
More data that might be of use:
So, this is my local testing environment according to phpMyAdmin:
Database server
---------------
Server: Local codeTech Server (Localhost via UNIX socket)
Server type: MySQL
Server version: 5.5.34-0ubuntu0.13.04.1 - (Ubuntu)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
Web server
----------
Apache/2.2.22 (Ubuntu)
Database client version: libmysql - 5.5.34
PHP extension: mysqli
And here is my production server's environment:
Database server
---------------
Server: Sam's Remote Server (XXX.XXX.XXX.XXX via TCP/IP)
Server type: MySQL
Server version: 5.1.70-cll - MySQL Community Server (GPL)
Protocol version: 10
User: [email protected]
Server charset: UTF-8 Unicode (utf8)
Web server
----------
Apache/2.2.22 (Ubuntu)
Database client version: libmysql - 5.5.34
PHP extension: mysqli
I'd also be willing to post some portions of phpinfo for both servers, if that would help.
A few things I gleamed from phpinfo that may help diagnose the problem:
Local:
- PHP Version 5.4.9-4ubuntu2.3
- Zend Engine v2.4.0
Production:
- PHP Version 5.3.26
- Zend Engine v2.3.0, Copyright (c) 1998-2013 Zend Technologies
- with the ionCube PHP Loader v4.2.2, Copyright (c) 2002-2012, by ionCube Ltd., and
- with Zend Guard Loader v3.3, Copyright (c) 1998-2010, by Zend Technologies
- with Suhosin v0.9.33, Copyright (c) 2007-2012, by SektionEins GmbH
- This server is protected with the Suhosin Extension 0.9.33
I suspect that this "Suhosin" extension might have something to do with it, though I'm not quite sure how...
Their page (http://www.hardened-php.net/suhosin/a_feature_list.html) suggests there may be a few "protections" in place:
- Transparent protection of open phpinfo() pages
- EXPERIMENTAL SQL database user protection
- Filtering Features
- Configureable action on violation
- just block violating variables
- send HTTP response code
- redirect the browser
In fact, this sounds highly suspect! I will probably have to have a talk with my sysadmin about this thing; unfortunately, the documentation on Suhosin is too sparse for me to know if it is actually the culprit, so I don't want to rule out everything else just yet.
Still, it might be important. These are the settings for Suhosin; they don't really make much sense to me, but maybe some PHP wizards can highlight some important keywords:
Directive Local Value Master Value
suhosin.apc_bug_workaround Off Off
suhosin.cookie.checkraddr 0 0
suhosin.cookie.cryptdocroot On On
suhosin.cookie.cryptkey [ protected ] [ protected ]
suhosin.cookie.cryptlist no value no value
suhosin.cookie.cryptraddr 0 0
suhosin.cookie.cryptua On On
suhosin.cookie.disallow_nul 1 1
suhosin.cookie.disallow_ws 1 1
suhosin.cookie.encrypt Off Off
suhosin.cookie.max_array_depth 50 50
suhosin.cookie.max_array_index_length 64 64
suhosin.cookie.max_name_length 64 64
suhosin.cookie.max_totalname_length 256 256
suhosin.cookie.max_value_length 10000 10000
suhosin.cookie.max_vars 100 100
suhosin.cookie.plainlist no value no value
suhosin.coredump Off Off
suhosin.disable.display_errors Off Off
suhosin.executor.allow_symlink Off Off
suhosin.executor.disable_emodifier Off Off
suhosin.executor.disable_eval Off Off
suhosin.executor.eval.blacklist no value no value
suhosin.executor.eval.whitelist no value no value
suhosin.executor.func.blacklist no value no value
suhosin.executor.func.whitelist no value no value
suhosin.executor.include.allow_writable_files On On
suhosin.executor.include.blacklist no value no value
suhosin.executor.include.max_traversal 0 0
suhosin.executor.include.whitelist no value no value
suhosin.executor.max_depth 0 0
suhosin.filter.action no value no value
suhosin.get.disallow_nul 1 1
suhosin.get.disallow_ws 0 0
suhosin.get.max_array_depth 50 50
suhosin.get.max_array_index_length 64 64
suhosin.get.max_name_length 64 64
suhosin.get.max_totalname_length 256 256
suhosin.get.max_value_length 512 512
suhosin.get.max_vars 100 100
suhosin.log.file 0 0
suhosin.log.file.name no value no value
suhosin.log.phpscript 0 0
suhosin.log.phpscript.is_safe Off Off
suhosin.log.phpscript.name no value no value
suhosin.log.sapi 0 0
suhosin.log.script 0 0
suhosin.log.script.name no value no value
suhosin.log.syslog no value no value
suhosin.log.syslog.facility no value no value
suhosin.log.syslog.priority no value no value
suhosin.log.use-x-forwarded-for Off Off
suhosin.mail.protect 0 0
suhosin.memory_limit 0 0
suhosin.mt_srand.ignore On On
suhosin.multiheader Off Off
suhosin.perdir 0 0
suhosin.post.disallow_nul 1 1
suhosin.post.disallow_ws 0 0
suhosin.post.max_array_depth 50 50
suhosin.post.max_array_index_length 64 64
suhosin.post.max_name_length 64 64
suhosin.post.max_totalname_length 256 256
suhosin.post.max_value_length 1000000 1000000
suhosin.post.max_vars 1000 1000
suhosin.protectkey On On
suhosin.request.disallow_nul 1 1
suhosin.request.disallow_ws 0 0
suhosin.request.max_array_depth 50 50
suhosin.request.max_array_index_length 64 64
suhosin.request.max_totalname_length 256 256
suhosin.request.max_value_length 1000000 1000000
suhosin.request.max_varname_length 64 64
suhosin.request.max_vars 1000 1000
suhosin.server.encode On On
suhosin.server.strip On On
suhosin.session.checkraddr 0 0
suhosin.session.cryptdocroot On On
suhosin.session.cryptkey [ protected ] [ protected ]
suhosin.session.cryptraddr 0 0
suhosin.session.cryptua Off Off
suhosin.session.encrypt On On
suhosin.session.max_id_length 128 128
suhosin.simulation Off Off
suhosin.sql.bailout_on_error Off Off
suhosin.sql.comment 0 0
suhosin.sql.multiselect 0 0
suhosin.sql.opencomment 0 0
suhosin.sql.union 0 0
suhosin.sql.user_postfix no value no value
suhosin.sql.user_prefix no value no value
suhosin.srand.ignore On On
suhosin.stealth On On
suhosin.upload.disallow_binary 0 0
suhosin.upload.disallow_elf 1 1
suhosin.upload.max_uploads 25 25
suhosin.upload.remove_binary 0 0
suhosin.upload.verification_script no value no value
Thanks in advance to anyone who can help me troubleshoot this. And congrats on reading this far!
About your PHP configuration differences between environments, you should compare the results of a phpinfo() execution in devel and production environments. Depending on the version, you can have magic_quotes activated (BAD IDEA!) or other configurations affecting your code, just compare one by one and you'll get the differences, which you should eliminate in almost all cases (except display_errors and a few more that need to be different in production and development).
About MySQL: you need to have two different users, which you will use depending on the query:
Normally, you will use the "reading" user and you just change to "write" for the specific queries that need to write.
I don't think your problem is in PHP (but check the phpinfo) and positively is not in MySQL, my bet would be the Suhosin plugin and, maybe, a configuration in CakePHP that changes depending on your environment, but with the given information, I can tell no more.
I never heard about Suhosin and if I were you, my first step would be to deactivate it. Using PDO's binding funcions and php filters will be a secure option without extra plugins.