Secure database queries and mysqli ...

Just a nagging question... the database API’s do not make use of mysqli (I understand this is planned for Revolution), and use of the db->query() method is discouraged in favor of structuring queries using the db->select() method (or similar): http://wiki.modxcms.com/index.php/API:DBAPI

So, are the fundamental db queries preparing statements to help avoid sql-injection and increase query speed? Is there any data-filtering going on inside the MODx db api internals? I’ve gotten burned by sql-injection attacks, so I really want to prepare my queries and filter data whenever possible.

How secure is the MODx database API? Thanks for your thoughts.

Quote from: Everett at Jan 06, 2009, 06:06 PM

Just a nagging question... the database API’s do not make use of mysqli (I understand this is planned for Revolution), and use of the db->query() method is discouraged in favor of structuring queries using the db->select() method (or similar): http://wiki.modxcms.com/index.php/API:DBAPI

MODx will not support mysqli; Revolution supports MySQL via the PDO_mysql driver or via xPDO’s PDO emulation code which uses the mysql extension.

Quote from: Everett at Jan 06, 2009, 06:06 PM

So, are the fundamental db queries preparing statements to help avoid sql-injection and increase query speed? Is there any data-filtering going on inside the MODx db api internals? I’ve gotten burned by sql-injection attacks, so I really want to prepare my queries and filter data whenever possible.

The DBAPI does nothing to help prevent sql-injection or increase query speed. They are convenience only. PDO (and xPDO) and it’s prepared statements will address this. You still need to make sure you cleanse any user-input you send to the DBAPI functions.

Quote from: Everett at Jan 06, 2009, 06:06 PM

How secure is the MODx database API? Thanks for your thoughts.

As secure as the code you write with it, same as if you were using the mysql or mysqli extension directly.

Thanks for the thorough reply. When security has come into question, I’ve always fallen back on mysqli, but it requires PHP5 and MySQL 5 if I remember correctly. Does anyone know of the benefits vs. drawbacks between the mysql and mysqli extensions?

I think that there is common misconception that by using the DBAPI it is automatically safe. The DBAPI does not add any measures like addslashes or any encoding. Take the eForm2db snippet, it demonstrates no security measures in the instructions and you need to read almost the entire thread before this is mentioned. I am guessing that a large number of people who have integrated that method are not entirely secure. I was one of them at first since I thought the API was safer than hand coding 

IMO I think it would be nice if the DBAPI had a safeguard option. When a tool is available for noobies, like an API, it would be nice if it helped prevent security issues. Or is the DBAPI only for seasoned pros?

My general rule of thumb has become to never assume that there is a safeguard unless I am sure there is.

I think that mysqli is an API and has a lot more functions to it and the standard mysql is a function (not sure ?). mysqli will work on MySQL 4.1+

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above.

it would be nice if the DBAPI had a safeguard option. When a tool is available for noobies, like an API, it would be nice if it helped prevent security issues.

I totally agree. I know from my own experience that learning a new tool or a new API can be such an exhausting experience that usually I’m so glad to just make it work at all... I don’t even think about security until later. And for some of these technologies I have absolutely no clue as to how they might be exploited so I don’t know if I’m walking through a minefield.

A setting somewhere that allows for a safe mode to be disabled/enabled would be nice for some things like this. I’m working on a CRUD interface for MODx, and I’m working to build that type of thing in... javascript and php regexes and prepared queries. The prepared statements alone are huge when it comes to preventing injection attacks. Using htmlentities() when displaying user-input data are probably the biggest two things that come to mind.

This is why PDO was created and mysql/mysqli extensions should IMO be obsolete in MODx core code, as well as components. This is also why xPDO was developed (in conjunction with the desire to make MODx portable to other database platforms), which MODx Revolution uses exclusively for database access, including providing emulated PDO functionality (and associated security safeguards) for PHP 4 and PHP 5 environments without PDO configured. PDO’s support of prepared statements (which is btw what mysqli provides that mysql doesn’t) is what simplifies coding for database developers, allowing them to focus on logic rather than cleansing user input. And xPDO’s object validation adds an additional layer of protection, as well.

PDO’s support of prepared statements (which is btw what mysqli provides that mysql doesn’t) is what simplifies coding for database developers, allowing them to focus on logic rather than cleansing user input. And xPDO’s object validation adds an additional layer of protection, as well.

That sounds great. With MODx I need to watch what I ask for since it might already be available

I really need to reed up on xPDO. I have been curious about it for some time, and now I find myself working more and more with additional data tables, maybe it is time to check it out more seriously.

Everett: your CRUD interface project sounds very interesting.

Thanks, OpenGeek for the thorough information (as per usual).

dev_cw, I’ll keep you (and the community posted) with my CRUD project. I need to develop access for a project I’m working on. The big nut to crack I figured out and posted the details in this thread (namely reading unique keys from url segments):
http://modxcms.com/forums/index.php/topic,31477.msg193727.html

To integrate with MODx, you’d need to do an Apache RewriteRule, but that’s not so bad... I’ll be writing a Module with some associated Snippets to handle access to external databases or custom tables in an MVC fashion. I haven’t seen anything out there like it... I spoke with some folks about integrating something like Code Igniter into MODx, but after working with CI’s scaffolding, I realize it’s not immediately usable in production because it’s not secure (the CI folks recommend deleting the scaffolding access as soon as you have data in... it’s meant only as a dev tool). CakePHP and Symfony were just nasty steep learning curves, but I haven’t come across any easy way to get CRUD access to database tables, and certainly nothing that has been ported over to MODx. So... stay tuned. I’ll definitely be making some more tutorial videos about this when the time comes. If anyone has something already built, then please, let me know... I tend to enjoy reinventing the wheel, but it’s not always time-efficient.

Quote from: Everett at Jan 07, 2009, 06:13 PM

dev_cw, I’ll keep you (and the community posted) with my CRUD project. I need to develop access for a project I’m working on. The big nut to crack I figured out and posted the details in this thread (namely reading unique keys from url segments):
http://modxcms.com/forums/index.php/topic,31477.msg193727.html

To integrate with MODx, you’d need to do an Apache RewriteRule, but that’s not so bad... I’ll be writing a Module with some associated Snippets to handle access to external databases or custom tables in an MVC fashion. I haven’t seen anything out there like it...

You only need rewrite rules to make a custom friendly URL interface to a single MODx document (which is technically a View Controller btw) which turns extra parameters (beyond the modx q= param) into additional url segments. This can be genericized to make this kind of thing easy to do with any particular document that has a script on it that works this way.

And I have tons of snippets that provide customized form access to manipulating custom data tables in MODx, and even though none of them have a friendly url interface like you are describing, that could easily be added to any one of them based on the query string parameters they accept.

Quote from: Everett at Jan 07, 2009, 06:13 PM

I spoke with some folks about integrating something like Code Igniter into MODx, but after working with CI’s scaffolding, I realize it’s not immediately usable in production because it’s not secure (the CI folks recommend deleting the scaffolding access as soon as you have data in... it’s meant only as a dev tool). CakePHP and Symfony were just nasty steep learning curves, but I haven’t come across any easy way to get CRUD access to database tables, and certainly nothing that has been ported over to MODx. So... stay tuned. I’ll definitely be making some more tutorial videos about this when the time comes. If anyone has something already built, then please, let me know... I tend to enjoy reinventing the wheel, but it’s not always time-efficient.

Again, xPDO was created after my horrible experiences with existing CRUD systems, including Propel and Doctrine (both of which have been integrated into Symfony to some extent). I think once more people understand what it provides, i.e. a jumpstart tool for the M in MVC, and that MODx is a great adaptation of the Views and Controllers already, we’ll see some great custom components appear on the scene.

OpenGeek- thanks again for the helpful response. Man, I can really commiserate with you about the horrible experiences with existing Crud frameworks. CakePHP hurt my head and Symfony was a traumatizing experience.

I have tons of snippets that provide customized form access to manipulating custom data tables in MODx

Do you mind pointing me towards a couple? I want to look at what’s already available... if I don’t have to code anything new, that’d be great.