In php with mysql can you replace $var with :var?

Question

I searched on the topics with the same name here but I didn't find anything relevant with what I have. I follow a tutorial and in a sql

$sql .= "WHERE event_id = :id LIMIT 1"

I saw that the author used :id. I assumed that should be $id. I don't know this php notation. Please, someone tell, me what is going on? Thank you.

This is called **parameter binding.** http://php.net/manual/en/pdostatement.bindparam.php There are two ways 1) named parameter `:name` or 2) positional parameters `?` — Lukasz Szozda, Nov 12 '15 at 15:07
This is parameter binding, and is a part of [PHP Data Objects (PDO)](http://php.net/manual/en/book.pdo.php) - one of the preferred ways to talk to a database in PHP. — samlev, Nov 12 '15 at 15:08
It's not, strictly speaking, a PHP notation: as far as the PHP engine is concerned, this is a normal string with no inline replacements. It's not replaced during normal string handling, but handled specially inside the PDO layer. — Piskvor left the building, Nov 12 '15 at 15:23

score 3 · Accepted Answer · edited May 23 '17 at 12:29

This is called parameter binding. The purpose is to prevent SQL Injections.

Example:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

http://php.net/manual/en/pdostatement.bindparam.php

Here is an good explanation: How can I prevent SQL injection in PHP?

score 2 · Answer 2 · answered Nov 12 '15 at 15:21

$sql = "UPDATE client SET password = :password, phone = :phone  WHERE id = :id;";

$query = $this->pdo->prepare($sql);

$params = array(
        ':id' => $ClientId, 
        ':password' => $password,
        ':telephone' => $phone
); 

$query->execute($params)

It's the syntax for a prepared statement for mySQL with PDO. You can do:

$sql = "UPDATE client SET password = $password, phone = $phone  WHERE id = $ClientId;";
$query = $this->pdo->query($sql);

I use a class with "extends PDO" and on the construct function, I init the connection with "$this->pdo = new PDO($dsn, $user, $pass, $options);"

score 1 · Answer 3 · answered Nov 12 '15 at 15:19

1

Thank you very much lad2025 and samlev for telling me that this is called parameter binding. I found an example very easy for others that maybe will have the same question like me:

$sth = $dbh->prepare ("INSERT INTO animal (name, category)
VALUES (:name, :cat)");
$sth->bindValue (":name", "ant");
$sth->bindValue (":cat", "insect");
$sth->execute ();
$sth->bindValue (":name", "snail");
$sth->bindValue (":cat", "gastropod");
$sth->execute ();

answered Nov 12 '15 at 15:19

dragon

1,212
13
24

1

if you are binding by value - which is good then just pass the array of values directly to `execute` rather than binding. i.e. `$sth->execute(array('name' => 'ant', 'cat' => 'insect'));` and it will do _exactly_ the same thing. – Ryan Vincent Nov 12 '15 at 15:28

In php with mysql can you replace $var with :var?

3 Answers3