using a PHP string in mysql, isset not solving the undefined vairable issue

Question

I am trying to take some data and compare it against a MYSQL statement. My data is a string which with my limited knowledge is causing me difficulties. Before anyone closes this question and refers me to the main SO question about an unidentified offset or the like, I have tried that and followed the isset but still am having problems. Here is what I have:

The data is a query string. string(125) "Customer_FName=r&Customer_LName=r&Customer_MobileNumber=2&Customer_Email=r%40rco.com&Reservation_Message=&C_Preference_Notes=" 0 to get this into my PHP function, I used $form_data = $_POST['form_data'];.

I then used this code:

parse_str($form_data, $data);
$test=isset($data['Customer_FName']) ? $data['Customer_FName'] : '';
$c_duplicate_check=$wpdb->get_results($wpdb->prepare("SELECT * FROM Customers WHERE Customer_FName=$test"));
echo($c_duplicate_check);

This produces the undiexined index despite using the isset. I have also tried:

$c_duplicate_check=$wpdb->get_results($wpdb->prepare("SELECT * FROM Customers WHERE Customer_FName='$data["Customer_FName"]'"));
echo($c_duplicate_check);

Same issue. How do I make this work? I feel like I have read the whole internet on this.

P.S. vardump($data) I get -

array(6) { 
    ["Customer_FName"]=> string(3) "ted" 
    ["Customer_LName"]=> string(5) "smith" 
    ["Customer_MobileNumber"]=> string(12) "555-555-5555" 
    ["Customer_Email"]=> string(7) "e@e.com" 
    ["Reservation_Message"]=> string(0) "" 
    ["C_Preference_Notes"]=> string(0) "" 
}

Answer 1

Using the information provided, I put the following into an online php sandbox:

$form_data = "Customer_FName=r&Customer_LName=r&Customer_MobileNumber=2&Customer_Email=r%40rco.com&Reservation_Message=&C_Preference_Notes=";
parse_str($form_data, $data);
var_dump($data);
$test=isset($data['Customer_FName']) ? $data['Customer_FName'] : '';
var_dump($test);

This generated the following result:

array(6) {
  ["Customer_FName"]=>
  string(1) "r"
  ["Customer_LName"]=>
  string(1) "r"
  ["Customer_MobileNumber"]=>
  string(1) "2"
  ["Customer_Email"]=>
  string(9) "r@rco.com"
  ["Reservation_Message"]=>
  string(0) ""
  ["C_Preference_Notes"]=>
  string(0) ""
}
string(1) "r"

So, aside from the provided data not matching the var dump that you provided for $data, you can clearly see that you can successfully extract the data from $form_data.

The undefined index is therefore being generated in your (malformed) call to $wpdb->prepare().

I took a simplified version of the prepare method and put it in the sandbox:

<?php
        //Enter your code here, enjoy!

$form_data = "Customer_FName=r&Customer_LName=r&Customer_MobileNumber=2&Customer_Email=r%40rco.com&Reservation_Message=&C_Preference_Notes=";
parse_str($form_data, $data);
var_dump($data);
$test=isset($data['Customer_FName']) ? $data['Customer_FName'] : '';
var_dump($test);

function prepare( $query, ...$args ) {
    $passed_as_array = false;
    if ( is_array( $args[0] ) && count( $args ) === 1 ) {
        $passed_as_array = true;
        $args            = $args[0];
    }
    foreach($args as $arg) {
        print $arg;
    }
}

prepare("SELECT * FROM Customers WHERE Customer_FName=$test");

And got the following result:

array(6) {
  ["Customer_FName"]=>
  string(1) "r"
  ["Customer_LName"]=>
  string(1) "r"
  ["Customer_MobileNumber"]=>
  string(1) "2"
  ["Customer_Email"]=>
  string(9) "r@rco.com"
  ["Reservation_Message"]=>
  string(0) ""
  ["C_Preference_Notes"]=>
  string(0) ""
}
string(1) "r"
<br />
<b>Notice</b>:  Undefined offset: 0 in <b>[...][...]</b> on line <b>12</b><br />

Bingo! There's the undefined offset. You have successfully found a place where wordpress did not user-proof their code.

Now, what's the fix? Use the prepare method correctly.

The documentation gives some really sucky examples, so here's what you need to know:

You must supply at least two arguments:

wpdb::prepare( string $query, mixed $args )

Placeholders are explained this way:

Uses sprintf()-like syntax. The following placeholders can be used in the query string: %d (integer) %f (float) %s (string) All placeholders MUST be left unquoted in the query string. A corresponding argument MUST be passed for each placeholder.

So for your query, you would use

$wpdb->prepare("SELECT * FROM Customers WHERE Customer_FName=%s",$test);

I don't use wordpress, so I'm guessing on this, but the rest of your sample script should probably look like this:

$c_duplicate_check = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM Customers WHERE Customer_FName=%s",
        $test
    )
);
print_r($c_duplicate_check); // result will be an array, so use print_r

Note, WordPress doesn't really prepare queries; they are just escaping values. Why they don't fix this, I have no idea. Just something to keep in mind, because if they ever do fix it you'll want to upgrade immediately. If I were to use WP, I'd undoubtedly roll my own CRUD object.

Answer 2

You are using the prepared statement wrong - the way you are doing it does not protect you from SQL injections.

Read up on the topic as it is DB 101 in many languages, not just PHP

Example prepared query using mysqli:

  $mysqli = new mysqli();
  $mysqli->connect('hostname','username','password','database');
  $query = "SELECT * FROM Customers WHERE Customer_FName=(?)";
  $statement = $mysqli->prepare($query);
  $statement->bind_param('s',$Customer_FName);
  $statement->execute();
  $result = $statement->get_result();
  $result = $result->fetch_all();
  $statement->close();
  var_dump($result);