Sanitizing a custom query's clauses

Question

I'm trying to build a custom query much like the one found in WordPress' native get_posts() inside wp-includes/query.php, meaning something along these lines:

global $wpdb
SELECT *
FROM $wpdb->my_custom_table custom
WHERE 1=1
$conditions
$orderby

I want to be able to apply_filters to the variables $conditions and $orderby, again much like WP's own functionality, so a plugin might add their own AND custom.column = 'foo' and such via filters. But there seems to be a glaring vulnerability to that approach: what if a filter that comes from a plugin inserts malicious code into that query?

I know about the $wpdb->prepare function, but as far I understand that sanitizes only the arguments that are passed to the query, and not the clauses themselves. It makes sense, because queries that should benefit from prepare are the ones with arguments passed dynamically via user input.

Anyway, here are my doubts: is my concern trivial, meaning that I can't be 100% safe when there will always be plugins with direct access to the code and the database at runtime? Is there even such a thing as a malicious plugin (well, I'm sure there is, but should I code with them in mind)? But if I should indeed be concerned, is there a way to sanitize the whole query and not just the arguments so I can achieve what I want (i.e. how does WordPress achieve it)? Or should I just try a different approach, even though it would mean sacrificing flexibility?

Answer 1

I would suggest to think in this way: if I can not sanitize whole query (as you can not, what does sanitized mean? Is DELETE or DROP malicious or wanted query? Your plugin would have to be able to determinate the intended purpose of each query ant it is unreachable.), you can predict it's content.

Your apply_filter function can take 4 arguments, when first is either AND or OR (you wont include argument stright, but you'll check it's value agains variable defined in your plugin and insert value of that "yours" variable. Second argument is string not containting semicolon nor =, nor quotes (you can str_replace them or reject on the base of strpos), next arg will be much the same as first, but will check for =/!=/>/=/<= and the last one will be the same as second one.