sql injection prevention methods

[3l3ktr0n]

Does e107 have any integrated measures for preventing sql injection?

[Deltik]

@3l3ktr0n: e107 does have some support for prepared statements; however, the interface could be improved.

For legacy reasons, most client code that builds SQL queries uses \e_parse::toDB() to escape untrusted input.

It is my wish to move to only prepared statements in the future, but part of the challenge is that the output format of \e_parse::toDB() is loaded with a lot of baggage, so cleanly separating these usages in client code will require a lot of educated guesses and database migrations, which could cause data loss if done improperly.

[Deltik]

Right, that's what I meant by "\e_parse::toDB() is loaded with a lot of baggage". It escapes quotes as if they were HTML, even for data types that aren't HTML.

There is currently no official guidance on how to reconcile this complication, but my personal view is to use prepared statements where possible. \e_db_pdo::db_Query() has documentation on e107's limited support for prepared statements.

[3l3ktr0n]

Well I ain't the right person to tell as everyones grandma codes better than me.. but in my opinion this legacy commitment is holding back e107s full potential. You can't cater to everyone. It's like trying to be the best ay everything. You can be the best at one thing or weak at everything. I'd ditch the chatbox and replace it with a messenger (something facebook like), instead of just display all the comments and forums posts maybe create a user timeline, add somekind of group functionality maybe? It's nice to get notifications in your mailbox but it'd be even nicer to get them on the site as seperate notification system or via pm. Some of the social networking themes are kind of the norm these days. But that's my opinion. If this where to change I think e107 would blow other cms out of the water. It feels as it is still in the 2000's.
This isn't to bring anyone down. Don't get me wrong I LOVE e107 BUT DAMN IS THIS ONE HARD RELATIONSHIP. My to cents if anyone cares. Maybe even work in the direction of being a framework rather than a cms?

[Deltik]

I know what you mean. It's hard to attract good developers due to how ancient the codebase is. I am a developer with lofty visions for e107, but I have too many time commitments elsewhere to be able to do the overhauls necessary to modernize the CMS.

[3l3ktr0n]

is it me or only pdo has some kind of support for prepared statements but mysql has none?

[Deltik]

That's right. e_db_mysql currently does not support prepared statements, but e_db_pdo does. I don't believe there is a reason to use e_db_mysql at all, though.

[3l3ktr0n]

$sql->select('user', 'user_id, user_name', 'user_id=:id OR user_name=:name ORDER BY user_name', array('id' => 999, 'name'=>'e107'));

has anyone tried binding like this from the manual?

[3l3ktr0n]

am I missing something or?

[Deltik]

Are you using e107 v2.2.0 or newer? The output you showed looked like it came from the old e_db_mysql class, which shouldn't even be possible to use without modding the core. e_db_pdo::select() got the ability to bind parameters in starting in e107 v2.2.0.

[Jimmi08]

Isn't here missing WHERE? SELECT game_id, user_id, review_id FROM e107_game_comment game_id=:game AND review_id=:review AND user_id=:user

[Deltik]

Yes, the object was of type the old e_db_mysql, and they called

public function e_db_mysql::select(
     $table,
     $fields = '*',
     $arg = '',
     $noWhere = false,
     $debug = FALSE,
     $log_type = '',
     $log_remark = '' ): false|int|string

with $noWhere set to a truthy value, which removes the WHERE.

[3l3ktr0n]

oh man, I was on 2.2.0 but for the db it was still mysql_class. Might have been an error when updating while back. Now on 2.3.2 and all is good. Thanks