[postgis-users] Problems with PHP and PostgreSQL/PostGIS

[Mark Cave-Ayland]

On Fri, 2007-04-13 at 14:55 +0200, Gerald Baumgartner wrote:
> I have renamed the tablename, but now there are other errors:
> 
> Warning: pg_query() [function.pg-query]: Query failed: ERROR: column
> "date_start_fahrt" is of type date but expression is of type integer
> at character 36 HINT: You will need to rewrite or cast the expression.
> in C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on line 103
> 
> Warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid
> input syntax for type numeric: "" in C:\ms4w\Apache\htdocs\GpsDaten
> \GPS_Daten_einlesen.php on line 112
>  
> 
> php Code:
>  
>     $timestamp=time();
>     $datum=date("d/m/y",$timestamp);
> 
>     if($z_eintraege==2)
>     {
>        $sql_befehl="INSERT INTO
> fahrtenbuchtabelle(fid,date_start_fahrt,date_insert,strecke) VALUES
> ($z_eintraege,$datum,$datum,GeometryFromText('LINESTRING($breitengrad1
> $laengengrad1, $breitengrad $laengengrad)',4326))"; 
>        $res=pg_query($connection,$sql_befehl);
>        $sql_befehl_select="SELECT max(FID) as fid from
> fahrtenbuchtabelle";
>        $res_fid=pg_query($connection,$sql_befehl_select);
>        $fahrtnummer=pg_result($res_fid,"fid"); 
>      
>     }
>     if($z_eintraege>2)
>     {
>       $sql_befehl_append="UPDATE fahrtenbuchtabelle SET
> strecke=AddPoint(strecke,GeomFromText('POINT($breitengrad
> $laengengrad)',4326)) where fid='$fahrtnummer'"; 
>       $result=pg_query($connection,$sql_befehl_append);
>     }
> 
> Gerald


Hi Gerald,

As Regina points out, you need to quote the date field with apostrophes
in the VALUES clause since the date needs to be passed in as a string.

Also a word of warning: you should look very carefully at your code; by
creating strings using "s and including variables as $var, it is
extremely likely that your code is vulnerable to an SQL injection attack
if register_globals is enabled and your variables are being passed in as
CGI parameters to your PHP page.


Kind regards,

Mark.