0

Im am trying to build a dynamic prepared statement so that I can reuse the code. I am running into a problem when using %?% in my prepared state as it used LIKE. My code is as follows:

$where = " First_Name LIKE '%?%' ";
$vals = array('Mike');
$type = 's';

$dbd = mysqli_stmt_init($dbconnection);
if (mysqli_stmt_prepare($dbd, "SELECT * FROM Contacts WHERE $where" )) {
mysqli_stmt_bind_param($dbd, $type, ...$vals);
if (!mysqli_stmt_execute($dbd)) {
echo "Execute Error: " . mysqli_error($dbconnection);
        } else {
            //do nothing
        }
    } else {
        echo "Prep Error: " . mysqli_error($dbconnection);
    }
mysqli_stmt_get_result($dbd);

So when I use "First_Name = ?" it works fine so I think my issue is with the '%?%'. I have searched resolutions but couldn't find anything related to my dynamic prepared statement. Thank you for any help.

Aldwoni
  • 1,168
  • 10
  • 24
user982853
  • 2,470
  • 14
  • 55
  • 82
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. – tadman Jun 15 '17 at 15:34
  • @tadman: Did you miss the `prepare()` and `bind_param()`??? – AbraCadaver Jun 15 '17 at 15:37
  • @AbraCadaver Yeah, I guess I did because this is a really unusual way to do the binding. Good catch. – tadman Jun 15 '17 at 15:42
  • Im very new to prepared statements so thank you for the feed back. I have used mysqli_stmt_prepare and mysqli_stmt_bind_param above. Is this not correct? – user982853 Jun 15 '17 at 15:44
  • The example 6 of the PDO manual will show you where you are failing, http://php.net/manual/en/pdo.prepared-statements.php (the mysqli manual I find lacking a lot of information). In short the placeholder needs to be on its own. – chris85 Jun 15 '17 at 15:49
  • @chris85 PDO and `mysqli` have some similarities, but they work differently. – tadman Jun 15 '17 at 16:09
  • Quick question. So is my code safe or not? One comment said no and another comment said ok. Am I ok to bind params using (... ellipsis/unpack)? Again, I am new to prepared statements and development all together so I appreciate any feedback. Thank you. – user982853 Jun 15 '17 at 16:33
  • @tadman The placeholders in both need to be on their own though. The PDO manually clearly states that and shows how the binding should be. Mysqli has no such examples. – chris85 Jun 15 '17 at 16:37
  • @chris85 the placeholders (???) or the values (...$vals)? Im confused when you say placeholders need to be on their own because my code has ? placeholders for each variable. All my variables/values are stored in an array (built by looping through the post array) and then unpacked and binded using ... (elipses) in the prepared statement. Just a little confused when you say each "placeholder" and also "on their own" not quite sure I know what you mean exactly. Thanks for your help. – user982853 Jun 15 '17 at 17:21
  • Mysqli only has one placeholder, it is `?`. In your query though you don't have a placeholder, you have a string with a `?` in it. The driver needs to see the placeholder on its own, it then will put in the value and escape the anything around it as needed. The driver will not look in quotes or backticks. You can read more here, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php. – chris85 Jun 15 '17 at 17:49

1 Answers1

2

You need to bind the complete value, not just a portion of it. This means doing:

$where = "First_Name LIKE ?"

And then binding:

$vals = array('%Mike%');
tadman
  • 208,517
  • 23
  • 234
  • 262
  • Thank you so much for the straight forward answer to my question and not all the criticism regarding everything else. The code works as expected now and brings back the correct results. Now that I have the answer (thank you), I welcome the criticism as I do want to write good secure code. – user982853 Jun 15 '17 at 15:47
  • Using placeholder values is a good place to start, so you're on the right track here. If you're looking for more flexibility with writing queries, or even some abstraction from the task of writing them, a good ORM like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/5.4/eloquent) can help. [PHP the Right Way](http://www.phptherightway.com/) is a good general survival guide for PHP as well. – tadman Jun 15 '17 at 16:11