1

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.

2
  • 2
    you really need to use wpdb prepare, else this is a terribly insecure shortcode ( not to mention a lack of formatting etc )
    – Tom J Nowell
    Mar 10, 2018 at 0:11
  • Also, don't add a closing PHP tag at the very end of the file if there's no HTML afterwards, it can cause problems if there's whitespace and it's not necessary
    – Tom J Nowell
    Mar 10, 2018 at 0:19

2 Answers 2

2

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

4
  • thank you very much for detailed explanation and your reminder to think about security. So I have changed my query to prepare, but the statement does not work. I've reviewed the Class Reference at Wordpress.org ( codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results ) and some other issues here at stackexchange, but I still didn't found out why request does not perform.
    – Peter
    Mar 10, 2018 at 13:48
  • Here is my code: ` $myID = 'Beatles'; # $myID = "Beatles"; // alternative $simplelist = $wpdb->query( $wpdb->prepare( " SELECT Row1, Row2 FROM table WHERE Row1 = %d ORDER BY Row1 ASC", $myID ) ); `
    – Peter
    Mar 10, 2018 at 13:49
  • After this, foreach should ouptut each row like this: foreach($simplelist as $row) {OUTPUT here }
    – Peter
    Mar 10, 2018 at 13:53
  • Reviewing my code today, I noticed a drag-and-drop-mistake. Code is now working. Thanks again!
    – Peter
    Mar 11, 2018 at 17:59
-1

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;
   }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.