Wordpress shortcode attributes for database SELECT?

Question

I have created a small shortcode function with attributes to review if attributes are handled properly. That's the case and now I would like to use the attribute in a SELECT statement. But the database does not provide any values. The SQL statement without variable in the WHERE condition is okay.

To test shortcodes, I've added this three lines into my Wordpress text page, but nor of them is working:

 [simplelist text=Beatles]
 [simplelist text='Beatles']
 [simplelist text="Beatles"]

And here is my code from functions.php:

 <?php
 ....

 function get_list( $atts )
 { 
   $atts = shortcode_atts( array(
       'text' => '',
       ), $atts);


   global $wpdb;

   $simplelist = $wpdb->get_results("SELECT `Row1`, `Row2` 
                                      FROM  `table` 
                                      WHERE `Row1` = " . $atts[text] . "
                                      ORDER BY `Row1` ASC");

   $Result =   .... some stuff here ...
   return $Result;
 }
 add_shortcode('simplelist', 'get_list' );
 ?>

When I remove the WHERE condition, I get complete table results. I need some help in using attribute values in WHERE condition.

Answer 1

Here's the SQL query:

  $simplelist = $wpdb->get_results("SELECT `Row1`, `Row2` 
                                      FROM  `table` 
                                      WHERE `Row1` = " . $atts[text] . "
                                      ORDER BY `Row1` ASC");

Lets pretend we're the computer and run it in our heads, and the first thing that happens is:

$atts[text] // fatal error!

text isn't defined, you're missing quotes, but lets fix that and we run into a new issue. If this value is test the result is:

                              SELECT `Row1`, `Row2` 
                                      FROM  `table` 
                                      WHERE `Row1` = test
                                      ORDER BY `Row1` ASC

test is missing quotes, which is why your query doesn't work.

The Massive Security Hole

Well you might be thinking "But Tom! Lets just surround it in quotes and it'll be all fine!", but we have a problem. There's no preparing of the statement, so all we need to do is break out of the quotes and insert arbitrary SQL statements. Now anywhere that renders shortcodes can be used to dump any table, drop tables, modify data, and so on.

To fix this, and prevent the original problem, use wpdb prepare, e.g.:

$table_name = "wp_myTable";
$myID = 12;

$wpdb->query( $wpdb->prepare( "UPDATE `$table_name` SET `your_column_1` = 1 WHERE `$table_name`.`your_column_id` = %d", $myID ) );

prepare will make sure the value is safely inserted into the query string in the correct format with the right data type.

Or you can just use a custom post type and avoid all the pain, with the free archives/URLs/templates/UIs/caching/REST endpoints/etc

Answer 2

First of all, you seem to get a mysql error. $attr[text] is a string so you need to wrap it with simple quote:

Row1 = '{$atts[text]}'

Then, the $wpdb->get_results() function return by default an object.

If you want a PHP array as result :

use ARRAY_A as second argument like this : $wpdb->get_results( $query, ARRAY_A );

Complete function

function get_list( $atts )
{ 
    $atts = shortcode_atts( array(
            'text' => '',
         ), 
         $atts );


   global $wpdb;

   $query = "SELECT Row1, Row2 FROM  table WHERE Row1 = '{$atts[text]}' ORDER BY Row1 ASC ;";
   $results = $wpdb->get_results( $query, ARRAY_A );

   if( !empty( $results ) ){
      if( count( $results === 1 ) ){
          return $results[0];
      }else{
          return $results;
      }   
   }else{
      return false;
   }

}