php Question posted in
Official manual can be found here.

in PHP does pg_query_params takes into consideration the type of parameter passed ? ( question also for PDO an mysql respective calls)

From the documentation it is not clear to me what exactly pg_query_params does when non string parameters are given.

pg_query_params (PHP 5 >= 5.1.0, PHP 7, PHP 8)

pg_query_params — Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text (ref)

I suppose that: In case a parameter is a string it should just escape it and enclose it in ticks, In case the parameter is a number (e.g. integer or float), it should convert it to a string and should not enclose it in ticks. If it is a null value it should replace it with the string NULL without enclosing it in ticks, if it is a boolean it should replace it with true or false accordingly without ticks or with T and F depending on taste, in case it is an array with compatible elements it should convert to a respective expression (e.g. array[‘hi’,’sir’] or array[3.45,45.7]) and so on for other more exotic types (multidimensional arrays, associative arrays when the extension is activated etc).

The other interpretation would be that it only works correctly with strings.

So where is explained what exactly pg_query_params does when called from PHP ?

I suppose a similar behaviour will happen for the mysqli or PDO corresponding calls so a full answer may clarify it for a wider audience.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The code in php-src/ext/pgsql/pgsql.c for pg_query_params is:

    pgsql_result = PQexecParams(
 pgsql
,query
,num_params
,NULL 
,(const char * const *)params
,NULL
,NULL
,0
);

    The fourth parameter is NULL and the documentation of PQexecParams says that the fourth parameter that is: paramTypes[] specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string.

    This means that PHP does not take into consideration the type of the variable passed to the call, and thus this information is lost.

    The other thing that I noted is that somewhere in the code, the variables are transformed into strings with the call:

    zend_string *param_str = zval_try_get_string(tmp);

    This means that the formed query is not exactly what expected by a newcomer: so e.g.

    var_dump(pg_fetch_all(pg_query_params($db,'select $1 a,$2 b,$1=$2 c',[true,false])));

    returns

    array(1) {
  [0]=>
  array(3) {
    ["a"]=>
    string(1) "1"
    ["b"]=>
    string(0) ""
    ["c"]=>
    string(1) "f"
  }
}

    In my opinion, a more correct implementation of the call, should take into consideration the PHP type of the passed variable, and rely that information to the call in order to have the correct processing of it, cause the above call we do not compare true with false but "1" with "".

    More over the documentation of pg_query_params should be clarified telling that "parameters passed are converted to strings" and afterwards passed as string (supposing that the implementor likes to do so instead of the semantically correct approach).


  2. 0

    So where is explained what exactly pg_query_params does when called from PHP ?

    There is no obligation to explain it, however the sources should be freely available and that is what it defines it. Consult the sources and study them for the versions you have in use when you exactly want to know it.

    It could also be of benefit for you to make yourself comfortable with higher level concepts like prepared or parameterized database statements. The documentation of your database server may also be helpful here, not only it’s source code and that of all the libraries and PHP itself.

    As it has been already commented, the parameters are not part of the SQL command text.

    Or as written very early on the documentation page:

    pass parameters separately from the SQL command text

    This is the explanation and I find it pretty exact and well explained to the wider audience, despite it is a short sentence. YMMV.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search