Re: Sql Object Model Parser & Sanitizer (was [RFC] is_literal)

> On Jun 22, 2021, at 10:35 AM, Stephen Reay <php-lists@koalephant.com> wrote: > >> On 22 Jun 2021, at 20:39, Mike Schinkel <mike@newclarity.net> wrote: >> >>> On Jun 22, 2021, at 9:00 AM, Kamil Tekiela <tekiela246@gmail.com> wrote: >>> >>> Hi Mike, >>> >>> Please don't do this. We already have PDO with prepared statements. The data must >>> be bound. This is the secure way of writing SQL queries. >> >> The problem is that over 40% of the web currently runs on PHP code that using mysqli. That >> CMS does not support PDO nor prepared statements, and is unlikely to switch to it anytime some in >> the foreseeable future. >> >> A SQL object model parser and sanitizer could more easily be used incrementally by that CMS >> since PDO does not share connections with mysqli (AFAIK, anyway.) >> > > (Resending from on-list address) > > Apparently you didn't know mysqli supports parameterised queries? > Wordpress could have adopted parameterised queries when they grudgingly switched to mysqli, > years after both it and PDO were introduced. Well, yes and no. I admit I had forgotten it because frankly the WordPress wp-db object wrapper used by 99.7%[1] of WordPress developers does not support it and given I had long ago realized I could not use them in WP I guess my memory blocked out the existence of prepared statements in mysqli. But it is not relevant in WordPress. Here is where (almost?) all SQL queries go to run in WordPress: https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L2050 Note there are no bind_param() or execute() calls anywhere in that file. Their is a prepare(), but WordPress rolled their own: https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L1302 So saying that parameterized query is a solution leaves out 40% of the web. Because some other guys are the intransigent ones here. But also because of much legacy code exists in the form of plugins and themes that do not support parameterized queries. HOWEVER, whether mysqli supports parameterised queries or not is all a moot point because parameterised queries do not allow for parameterizing field names or table names. And the point of this thread was to discuss how to mark SQL that has been composed at run-time to be "safe." Without being able to parameterize field names and table names parameterised queries are not a sufficiently complete solution. At least not from an is_literal()/is_trusted() perspective. > There’s zero reason to believe they would adopt this unless forced to. Exactly! WordPress is not going to change, so that leaves it up to PHP developers using WordPress to protect themselves. Since I am *assuming* that everyone on this list wants to minimize the number of security issues in PHP applications across the web by whatever reasonable means necessary, I also assume that pointing at WordPress and saying "It's their fault not ours" does not help achieve those security goals? So I am also assuming that finding a pragmatic solution that would allow PHP to be able to empower the developer in a way that WordPress won't might be worth considering. But tell me if I am assuming wrongly here. -Mike [1] Yes, I made that up out of whole cloth. But honestly in 10+ years working with WP I have almost never seen anyone use anything but the $wpdb provided by WP — except for one single plugin — and $wpdb does not support parameterized queries.