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
– Tom910, Commented Sep 13, 2015 at 11:10
You may be interested in this answer of a pretty similar question: stackoverflow.com/questions/31643297/… — greg
– greg, Commented Sep 13, 2015 at 17:07

Dan Belden · Accepted Answer · 2015-09-13 12:01:57Z

0

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();

edited Sep 13, 2015 at 12:01

answered Sep 13, 2015 at 11:06

Dan Belden

1,2171 gold badge10 silver badges20 bronze badges

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

2 Comments

Tom910 Over a year ago

:login can be as string and number. A PDO::PARAM_INT always translate to int

Dan Belden Over a year ago

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: stackoverflow.com/questions/1335081/…

Collectives™ on Stack Overflow

Prepared statement in PostgreSQL, string and number

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related