When we usually order the columns, we usually get OrderBy and order from the user’s input like $_REQUEST['orderby'] and $_REQUEST['order']. For example, the user wanted to order by ID in the ascending or descending order.
and we pass $_REQUEST['orderby'] and $_REQUEST['order'] to the SQL query like:
SELECT * FROM `table_name` ORDER BY $_REQUEST['orderby'] $_REQUEST['order'] LIMIT 10; The SQL Query above looks dangerous because it’s passing the user’s input directly to the query. We should never trust user input. So, we’ll sanitize them.
Sanitizing SQL OrderBy
We should sanitize the $_REQUEST['orderby'] to ensure the string is a valid SQL ‘order by’ clause. There’s a built-in WordPress function sanitize_sql_orderby.
SELECT * FROM `table_name` ORDER BY sanitize_sql_orderby( wp_unslash( $_REQUEST['orderby'] ) ) $_REQUEST['order'] LIMIT 10;
Whitelisting rather than sanitizing
We don’t always need to sanitize the input if we know the expected input and only allow the expected input to the SQL query.
$columns = [ 'ID', 'name', 'value', 'status' ];
$orderBy = isset( $_REQUEST['orderby'] ) && in_array( $_REQUEST['orderby'], $columns, true ) ? $_REQUEST['orderby'] : 'ID';
SELECT * FROM `table_name` ORDER BY $orderBy $_REQUEST['order'] LIMIT 10;
The above Query is as safe as sanitizing. We’re passing the $_REQUEST['orderby'] if only it strictly matches the columns we desire. So, we can trust $orderBy here.
Similarly, for the order. We already know that its value should be either desc or asc. So, we only allow these two values.
$order = [ 'asc', 'desc' ];
$order = isset( $_REQUEST['order'] ) && 'desc' === $_REQUEST['order'] ? 'desc' : 'asc';
SELECT * FROM `table_name` ORDER BY $orderBy $order LIMIT 10;This way, we don’t always need to sanitize the user input if we already know the expected input.
