How to insert null to Postgres from PHP

Question

I have been working on a project management system and I'm coding it with PHP and PostgreSQL 8.4 and Model-View-Controller.

To put you people in context I will explain from the root (as short as I can).

I have a class called Actividad that extends a database abstraction class to execute the queries.

    class Actividad extends AbstraccionBD
    {      
    
    /**
     * Constructor de la clase
     *
     * @param String  $nom  Nombre 
     * @param String  $des  Descripción
     * @param String  $fi   Fecha de inicio
     * @param String  $fc   Fecha de culminación
     * @param Float   $pon  Ponderación
     * @param Boolean $crit Crítica
     * @param String  $le   Lugar Ejeucución
     * @param String  $rec  Recursos
     * @param Integer $pe   Presupuesto estimado
     * @param String  $est  Estado
     * @param Integer $cal  Calificación
     * @param Integer $peje Presupuesto ejecutado
     * @param Integer $asis Asistencia
     */
    public function __construct($nom, $des, $fi, $fc, $pon, $crit, $le, $rec, 
                                $pe, $est, $cal, $peje, $asis)
    {

        //Asignamos todos los valores especificados al 
        //objeto
        $this->nombre = $nom;
        $this->descripcion = $des;
        $this->fecha_inicio = $fi;
        $this->fecha_culminacion = $fc;
        $this->ponderación = $pon;
        $this->es_critica = $crit;
        $this->lugar_ejecucion = $le;
        $this->recursos = $rec;
        $this->presupuesto_est = $pe;
        $this->estado = $est;
        $this->calificacion = $cal;
        $this->presupuesto_ejec = $peje;
        $this->asistencia = $asis;
        
    }

    public function insertarObjeto()
    {
        //Construimos el query de inserción de datos en la tabla
        //actividad; nótese que se llama al procedimiento almacenado
        //sga.max_num_actividad(int,int) para determinar el número
        //de la actividad que se esta insertando.
        $this->_query = 
            "INSERT INTO sga.actividades " . 
                "(proyecto, ejec_proyecto, num_actividad, nombre, " . 
                "descripcion, fecha_inicio, fecha_culminacion, " .
                "ponderacion, critica, lugar_ejecucion, recursos, " . 
                "prepuesto_estimado, estado, calificacion, " . 
                "presupuesto_ejecutado, asistencia) " . 
            "VALUES " . 
                "($this->proyecto, $this->ejec_proyecto, " . 
                "sga.max_num_act($this->proyecto, $this->ejec_proyecto) + 1, " . 
                "'$this->nombre', " . 
                "'$this->descripcion', '$this->fecha_inicio', " . 
                "'$this->fecha_culminacion', $this->ponderación, " . 
                "$this->es_critica, '$this->lugar_ejecucion', " . 
                "'$this->recursos', $this->presupuesto_est, " . 
                "'$this->estado', $this->calificacion, " . 
                "$this->presupuesto_ejec, $this->asistencia);";
                
        //Ejecutamos el query de inserción
        $this->ejecutarQuery();
    }

(I was asked to keep it 80 chars per line for the project.)

Table definition (no FK taken in consideration):

CREATE TABLE sga.actividades
(
    proyecto integer NOT NULL,
    ejec_proyecto integer NOT NULL,
    num_actividad smallint NOT NULL,
    nombre character varying(150),
    descripcion character varying(500),
    fecha_inicio date NOT NULL,
    fecha_culminacion date NOT NULL,
    ponderacion numeric (3,2) NOT NULL DEFAULT 0.00,
    critica boolean NOT NULL DEFAULT FALSE,
    lugar_ejecucion character varying(100),
    recursos character varying(250),
    prepuesto_estimado integer,
    estado sga.estados_actividad NOT NULL, -- Dominio estados_actividad
    calificacion integer,
    presupuesto_ejecutado integer,
    asistencia smallint, 
    CONSTRAINT actividad_pkey
        PRIMARY KEY(proyecto, ejec_proyecto, num_actividad)
);

Now, what I'm trying to do is to pass the values from the model, 'something' like this:

    $a = new Actividad('Actividad1','DescA1', '14-07-14','14-07-14',0.00, 
                'false',null,null,0,'ACT',null, null, null);

    //Dont worry im using __set method
    $act->proyecto = 1;
    $act->ejec_proyecto = 1;

    $a->insertarObjeto();

As you can see I'm passing some NULL values in the constructor because in the DB those values can be null, all cool so far.

When I try to run it I get this query:

INSERT INTO sga.actividades 
    (proyecto, ejec_proyecto, 
    num_actividad, nombre, 
    descripcion, fecha_inicio, 
    fecha_culminacion,
    ponderacion, critica, 
    lugar_ejecucion, recursos, 
    prepuesto_estimado, estado, 
    calificacion, presupuesto_ejecutado, 
    asistencia) 
VALUES 
    (1, 1, sga.max_num_act(1, 1) + 1, 
    'Actividad1', 'DescA1', '14-07-14', 
    '14-07-14', 0, false, '', '', 0, 'ACT', , , );

Here's my problem: this query will never run because I'm using NULL keyword from PHP and when it's converted into a string in the 'concatenation madness', it stays empty (nothing in the string), so Postgres (as expected) sends the syntax error in the (, , ,) part.

I need to replace those empty strings ('') with the NULL keyword so that Postgres recognizes and inserts it correctly.

Also, some of the values passed in the constructor are wrapped into single quotes in the insertarObjeto() function (they are character varying in the DB).

And in Postgres '' is not the same as NULL.

What I tried to fix this

One way is to put (N) if-else statements and concatenate the correct on each case (which is kind of ugly for future maintenance of the code and system expansions), something like this:

if(is_null($this->asistencia))
{
    $this->_query .= "null, ";
}
else
{
    $this->_query .= "$this->asistencia, ";
}

The problem in that approach is that there are to many attributes (and I have classes with far more that this one).

Another way I saw is to wrap null in single quotes in the constructor. That works for integer (bad solution I know), but those wrapped in single quotes already in the function will throw 'null' in the query, which is also wrong, i.e:

INSERT INTO sga.actividades 
        (proyecto, ejec_proyecto, 
        num_actividad, nombre, 
        descripcion, fecha_inicio, 
        fecha_culminacion,
        ponderacion, critica, 
        lugar_ejecucion, recursos, 
        prepuesto_estimado, estado, 
        calificacion, presupuesto_ejecutado, 
        asistencia) 
    VALUES 
        (1, 1, sga.max_num_act(1, 1) + 1, 
        'Actividad1', 'DescA1', '14-07-14', 
        '14-07-14', 0, false, 'null', 'null', 0, 'ACT', null ,null ,null );

Is there a better solution for this? Other than (n) if-else statements?

What pg_*() function(s) are you using? Show a really simple ~3-line example. — Dwayne Towell, Jul 21 '14 at 23:12
Ain't no such thing as "postgre". [The name is "PostgreSQL" or "Postgres" for short.](https://wiki.postgresql.org/wiki/Identity_Guidelines) — Erwin Brandstetter, Jul 21 '14 at 23:21
@ErwinBrandstetter, My bad i will update the title, i didn't notice the typo. Also i will try your solution about the prepared statements, they look like the way to go on this. — Gabriel Vitale, Jul 22 '14 at 00:17
@DwayneTowell im using ADODB for the execution of the postgres statements, i will check out how that library manages the prepared statements. — Gabriel Vitale, Jul 22 '14 at 00:20
I think that for future people finding this error i'll put the table definition has well. — Gabriel Vitale, Jul 22 '14 at 00:21
@ErwinBrandstetter, the prepared statements worked like a charm in this, and like you said the PHP null is used perfectly on them. Thank you very much. — Gabriel Vitale, Jul 22 '14 at 01:19

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Two possible solutions:

1. Omit columns

If you don't have defined column defaults, the default default (sic!) is NULL. You can just omit columns that should be NULL:

INSERT INTO sga.actividades 
        (proyecto, ejec_proyecto, 
        num_actividad, nombre, 
        descripcion, fecha_inicio, 
        fecha_culminacion,
        ponderacion, critica, 
        prepuesto_estimado, estado) 
VALUES 
        (1, 1, sga.max_num_act(1, 1) + 1, 
        'Actividad1', 'DescA1', '14-07-14', 
        '14-07-14', 0, false, 0, 'ACT');

Of course, if you alter column defaults for columns not mentioned here, you also alter the result of this INSERT - which may or may not be desirable.

2. Use prepared statements

Then you can use PHP NULL values. And it's a good idea anyway to sanitize input and prevent possible SQL injection.

pg_prepare($pg_conn, 'insert1', "INSERT INTO sga.actividades (proyecto, ejec_proyecto, ..., lugar_ejecucion, ...) VALUES ($1, $2, ...)");

pg_exec($pg_conn, 'insert1', array(1, 1, ..., NULL, ...));

More in the manual on pg_prepare and pg_execute.

How to insert null to Postgres from PHP

What I tried to fix this

1 Answers1

1. Omit columns

2. Use prepared statements

Linked