What does the formal argument of bindValue () indicate?

Sep 8, 2020 PHP MySQL PDO PDOStatement

What you can see in this article

What is bindValue () in the first place?

public PDOStatement :: bindValue (mixed $ parameter, mixed $ value [, int $ data_type = PDO :: PARAM_STR]): bool

Bind the value to the corresponding name or question mark placeholder in the SQL statement used in the prepared statement. PHP official reference

In a very rough summary, bindValue () is It is a function that can replace the “?” Part of the following SQL query statement with another character string. It’s a SQL injection measure.

$ sql =" insert into phptodo (name, done, priority) values (?, 0,?) ";

Especially prepared statements? PDO? If you are a person, you will be happy to refer to it because it is organized in this article.

SQL injection? Those who say, How to make a secure website will make you happy.

Note that bindValue () takes up to the third argument.

Think about each argument of # bindValue ()

Sample code


     $ name = "hoge"
     $ priority = "high"

     $ sql = "insert into phptodo (name, done, priority) values (?, 0,?)"; // SQL injection countermeasure placeholder (=?)
     $ stmt = $ dbh-> prepare ($ sql);

     $ stmt-> bindValue (1, $ name, PDO :: PARAM_STR);

     $ stmt-> bindValue (2, $ priority, PDO :: PARAM_STR);
     $ stmt-> execute ();

// $ sql = "insert into phptodo (name, done, priority) values ("hoge ", 0," high ")"
// becomes

First argument of bindValue ()

Specifies numerically which placeholder (the “?” In $ sql) to apply the function to.

For example, in the sample code, it is values (?, 0,?), So

If you want to change the first argument “?” In values => 1 If you want to change the third argument “?” In values => 2

It is described as. Note that the numbers start with 1 instead of 0!

Second argument of bindValue ()

Specifies what to assign to the placeholder specified in the first argument. Here, the variables $ name and $ priority are assigned.

Third argument of bindValue ()

Specifies what kind of data type the content specified in the second argument should be.

PDO :: PARAM_INT```PDO :: PARAM_STR etc.

These are Integer constants, for example PDO :: PARAM_STR represents an Integer 2. (For this reason, writing 2 instead of PDO :: PARAM_STR will do the same thing)

print (PDO: PARAM_STR)

// 2

Other constants are here

How to avoid using “?” In placeholders

Although it is a fluke, there is also a method to improve readability by making the placeholder an arbitrary character string.

Set “?” In the SQL statement as : hoge or: fuga In addition, specify the first argument of bindValue () as ": hoge " or ": fuga " instead of a number.

Sample code

    $ sql = "insert into phptodo (name, done, priority) values (: name, 0,: priority)";
    $ stmt = $ dbh-> prepare ($ sql);

    $ stmt-> bindValue (": name", $ name, PDO :: PARAM_STR);
    $ stmt-> bindValue (": priority", $ priority, PDO :: PARAM_STR);
    $ stmt-> execute ();

#Reference