Numerical PHP variables in MySQL queries

Question

I'm trying to build a query in a script that relies upon an object's attributes in order to retrieve the correct information. What I have is this:

$query = "SELECT fields FROM table WHERE fieldA  = $this->x";
//Processing of results here

I've seen queries like this used before with string variables but I'm not sure if the rules are different if you're using a variable that you know has a numerical value and the corresponding column for said value is declared as an integer or decimal. Would I need to include single quotes around $this->x?

It's probably about time you started learning a little bit of SQL.... and then a little bit about prepared statements and bind variables — Mark Baker
– Mark Baker, Commented Dec 22, 2015 at 15:45
try: $query = "SELECT fields FROM table WHERE fieldA = ".$this->x; — Gouda Elalfy
– Gouda Elalfy, Commented Dec 22, 2015 at 15:45
$this->x this is enough in where it can be string or integer np — user5621153
– user5621153, Commented Dec 22, 2015 at 15:46
When you put the payload data into the sql query string (instead of using prepared statements) the MySQL server parses that string, regardless of how it was constructed "within" php. SELECT ... WHERE foo=1 is valid, no need for single quotes (though MySQL's implicit type casting could handle foo='1'). So, if you're absolutely certain $this->x contains only digits (always) you can do that, "SELECT fields FROM table WHERE fieldA = {$this->x}". On the other hand, why bother when there are almost fool-proof prepared statements+named parameters? ;-) — VolkerK
– VolkerK, Commented Dec 22, 2015 at 15:51

Community · Accepted Answer · 2017-05-23 12:24:37Z

1

Use PDO to do that:

$user     = "username";
$password = "password";

$pdo = new PDO('mysql:host=localhost;dbname=dbname', $user, $pass);

$stmt = $pdo->prepare('SELECT fields FROM table WHERE fieldA = :value');
$stmt->execute(array('value' => $this->x);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Print the result:

echo '<pre>';
print_r($result);
echo '</pre>';

Or bind one value:

$sth->bindValue(':value', $this->x, PDO::PARAM_INT);

For MySQLi see: How can I prevent SQL injection in PHP?

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 22, 2015 at 15:59

EJW

3383 gold badges7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Monty Khanna · Accepted Answer · 2015-12-22 16:39:48Z

0

As you said $this->x is numeric. I make a small test. Its working fine.

class foo{
    public $x; 
    public function showQuery (){
        $this->x = 10;
        return "SELECT fields FROM table WHERE fieldA  = $this->x";
    }
}
$ob = new foo();
echo $ob->showQuery();

OUTPUT :

SELECT fields FROM table WHERE fieldA  = 10

Note : It depends upon your wish you want to use PDO or not. is it acceptable to use a standard string query and execute it with a mysqli database handler? : YES

answered Dec 22, 2015 at 16:39

Monty Khanna

1,1207 silver badges15 bronze badges

Collectives™ on Stack Overflow

Numerical PHP variables in MySQL queries

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related