Problem bindValue and NULL values

Question

Problem bindValue and NULL values

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a function like the following:

function xxx( $sql, $params ) {

    $query = $this->con->prepare( $sql );
    $num = count( $params );

    $pos = 0;
    for ( $i=1; $i <= $num; $i++ ) { 

        $param = $params[ $pos ];
        echo( "Parámetro " . $i . " -> " . $param . "<br>" ); //traza
        $query->bindValue( $i, $param ); //$query->bindParam( 1, $param )

        $pos++;
    }

    $query->execute( $params );

    // ...
}

Where some possible parameters could be:

$sql = "INSERT INTO mi_tabla VALUES(NULL, ?, ?)";
$params = [ "Foo", NULL ];

To insert a new record in a table with the fields: id, nombre y edad .

For edad an integer is expected, but it also accepts null values , the problem comes when you pass a value NULL , as a parameter, which does not bin. There is no error, the registration is simply not created.

I have tried to indicate the type of parameter as integer type (PDO::PARAM_INT) and type NULL (PDO::PARAM_NULL) , when the parameter is NULL , but without result.

This is the configuration for the connection:

$options = [
    \PDO::ATTR_DEFAULT_FETCH_MODE   => \PDO::FETCH_OBJ,
    \PDO::ATTR_EMULATE_PREPARES     => FALSE, //Inactives emulated prepares
    \PDO::MYSQL_ATTR_INIT_COMMAND   => "SET NAMES utf8"
];
$options[] = ( $this->env === ( 'dev' || 'test' ))
    ? array( \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION )
    : array( \PDO::ATTR_ERRMODE => \PDO::ERRMODE_SILENT );

try {
    return new \PDO( 
        'mysql:host=' . $db_host . 
        ';dbname=' . $this->db_name, 
        $this->db_user, 
        $this->db_pass, 
        $options 
    );

//...

Note : this example is simplified, the function must accept different statements with different groups of parameters.

php pdo

asked by Orici 03.04.2017 в 08:43

source

2 answers

Join two routers in VueJS How to copy a file that is in the Assets folder?

score 1 · Answer 1

I have found a solution.

When the parameter to be inserted, in a field that accepts an INT or NULL, is NULL, I convert it to string. Then it is necessary to use the validation rule for the NULL type, that is, the original function would look like this:

function xxx( $sql, $params ) {

foreach ( $params as $key => $value ) {

    if ( is_null( $value ))
        $params[ $key ] = "NULL";
}

$query = $this->con->prepare( $sql );
$num = count( $params );

$pos = 0;
for ( $i=1; $i <= $num; $i++ ) { 

    $param = $params[ $pos ];

    $validation = FALSE;
    if ( is_null( $param ) || $param == "NULL" )
        $validation = \PDO::PARAM_NULL; 

    $query->bindValue( $i, $param, $validation );

    $pos++;
}

$query->execute( $params );

// ...
}

The other option I had was to send as parameters to a second function zzz (), at the beginning of xxx (), the ones received by it and proceed to replace the questions directly with NULL, as part of the string, and eliminate, in turn, the NULL values of the $ params array.

score 0 · Answer 2

I've tested your situation with the following class, PHP-PDO , which I created myself and it works for me following way:

A. If you put NULL without quotes in the array

$datos=$mipdo->query(
                   "INSERT INTO tabla_prueba (columna1, columna2) 
                    VALUES (:val1, :val2)",
                    array("val1"=>"156","val2"=>NULL)
                  );

In this case I inserted NULL in colmuna2.

B. If you put NULL with quotes in the array

In that case, NULL is any string. Then the binding automatically converts it to zero 0 .

With this code:

$datos=$mipdo->query(
                   "INSERT INTO tabla_prueba (columna1, columna2) 
                    VALUES (:val1, :val2)",
                    array("val1"=>"157","val2"=>"NULL")
                  );

I insert the value 0 in columna2 .

And ... how do you do it?

This is the code that validates the bindings within the class mentioned above:

        # Asignar parámetros
        if (!empty($this->parametros)) {
            foreach ($this->parametros as $param => $value) {
                if(is_int($value[1])) {
                    $type = PDO::PARAM_INT;
                } else if(is_bool($value[1])) {
                    $type = PDO::PARAM_BOOL;
                } else if(is_null($value[1])) {
                    $type = PDO::PARAM_NULL;
                } else {
                    $type = PDO::PARAM_STR;
                }

From which it follows that, in effect, PDO has the ability to convert the parameters, in this case assigning 0 to a value that we pass as a string.

I would not, anyway, give that freedom to PDO, I do not like to delegate in the introduction of my data, so, before doing the binding I would validate the data in some way, and if there was the possibility of null, would pass them only as indicated in (A) , since, as we know, NULL and 0 are not the same thing.

You could also , if you want, modify the way the class validates the bindings, and say that if it is an empty string that, instead of binderate it as STRING do the binding as NULL , but that would be something personal, which will depend on each one and how the data are organized. Again, I insist that it is best to validate before passing the parameters .