Prepared statement in PostgreSQL, string and number

Question

php code:

$con = new PDO($this->dsn, $this->login, $this->password);
$stmt = $con->prepare($selectSql);

$stmt->execute(array(
  'login'    => $login
));

and sql query:

$selectSql = 'SELECT id 
              FROM public.users 
              WHERE (numberType = :login OR stringType = :login)';

The problem is that I do not find it desirable to edit PHP Code and add new variables to the example 'loginNumber' => (int) $login.

How can I fix it so that the SQL search result itself would be transferred to number value?

numberType = (:login)::Integer parameter did not work.

I need in sql query prejudge 2 different types :login. :login can be number, and string. Now the query is not executed and derision that type of data is not true. — Tom910, Sep 13 '15 at 11:10
You may be interested in this answer of a pretty similar question: http://stackoverflow.com/questions/31643297/pg-query-result-contains-strings-instead-of-integer-numeric/31740990#31740990 — greg, Sep 13 '15 at 17:07

Dan Belden · Answer 1 · 2015-09-13T12:01:57.560

I have revised my answer given new comments, you will need to detect what data format has been provided. This is tricky as it seems your data always comes as a string, even if the string contains a raw integer.

The sloppy way to do this is to do a quick is_numeric check... but this will parse any numeric value as true... even floats? On-top of that, variable binding assumes string by default in PDO, to bind anything else you must verbosely define it.

Here is some loose code to give you an idea, but you may have to improve the $isNumericLogin logic for more accurate results for numeric non-integer login values.

$con = new PDO($this->dsn, $this->login, $this->password);

// Generate correct query given the data format
$isNumericLogin = is_numeric($login);
$selectSql = $isNumericLogin ?
    'SELECT id FROM public.users WHERE number = :login' :
    'SELECT id FROM public.users WHERE string = :login';

$stmt = $con->prepare($selectSql);

// Bind the correct data format based
$isNumericLogin ?
    $stmt->bindValue('login', $login, PDO::PARAM_INT) :
    $stmt->bindValue('login', $login);

$stmt->execute();

:login can be as string and number. A PDO::PARAM_INT always translate to int — Tom910, Sep 13 '15 at 11:12
If your param is of type float/decimal then you have a problem. What data format is "number" in your database? See here for more info on your issue: http://stackoverflow.com/questions/1335081/what-is-the-best-way-to-bind-decimal-double-float-values-with-pdo-in-php — Dan Belden, Sep 13 '15 at 12:00

Prepared statement in PostgreSQL, string and number

1 Answers1