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?

http://php.net/manual/en/language.types.string.php#language.types.string.parsing — AbraCadaver, Dec 22 '15 at 15:45
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, Dec 22 '15 at 15:45
try: $query = "SELECT fields FROM table WHERE fieldA = ".$this->x; — Gouda Elalfy, Dec 22 '15 at 15:45
$this->x this is enough in where it can be string or integer np — , Dec 22 '15 at 15:46
Please read related topic http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — u_mulder, Dec 22 '15 at 15:47
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, Dec 22 '15 at 15:51
The thing about this is that this is part of an object and in the situation where this specific object gets created, no user input is taken. Is a prepared statement still recommended or would it be more efficient to do it the way I initially intended? — user3521737, Dec 22 '15 at 16:09
I am not clear with your question. What is the problem you are facing ? — Monty, Dec 22 '15 at 16:27
An object is created to display its corresponding database information on the page. No user input is taken nor required in order to do this. Should I still use a prepared statement in order to fetch the items' information from the database, or is it acceptable to use a standard string query and execute it with a mysqli database handler? — user3521737, Dec 22 '15 at 16:31
If you make selective judgements on the source of data to determine when you should and shouldn't use prepared statements/bind variables, you'll make a mistake (and you end up increasing code complexity having different methods to interact with the database in different circumstances).... be consistent, and always use prepared statements/bind variables — Mark Baker, Dec 22 '15 at 18:10

score 1 · Answer 1 · edited May 23 '17 at 12:24

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?

score 0 · Accepted Answer · answered Dec 22 '15 at 16:39

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

Numerical PHP variables in MySQL queries

2 Answers2