Getting “ERROR: invalid input syntax for type double precision” in PostgreSQL

1
votes

I use this script to insert data to my database:

<html>
<body>
    <?php
    include('koneksi.php');

    $gid = pg_escape_string ($_POST['gid']);
    $nama = pg_escape_string ($_POST['nama']);
    $alamat = pg_escape_string ($_POST['alamat']);
    $no_telfon = pg_escape_string ($_POST['no_telfon']);
    $email = pg_escape_string ($_POST['email']);
    $website = pg_escape_string ($_POST['website']);
    $longitude = pg_escape_string ($_POST['longitude']);
    $latitude = pg_escape_string ($_POST['latitude']);

    $query = "INSERT INTO perguruantinggi( gid, nama, alamat, no_telfon, email, website, longitude, latitude ) 
    VALUES ('" . $gid . "', '" . $nama . "', '" . $alamat . "', '" . $no_telfon . "', '" . $email . "', '" . $website . "', '" . $longitude . "', '" . $latitude . "')";
    $result = pg_exec($query);

    //$query = "INSERT INTO perguruantinggi (username, password, fullname, email, agama, no_hp) 
    //VALUES ('" . $username . "', '" . $password . "', '" . $email . "', '" . $fullname . "', '" . $agama . "', '" . $no_hp . "')" ;


    if (!$result) {
        $errormessage = pg_last_error();
        echo "Error with query: " . $errormessage;
        exit();
    }
    printf ("These values were inserted into the database - %s %s %s", $firstname, $surname, $emailaddress);
    pg_close();
    ?>
</body>

I found this error when I compile this script. I use double precision for coordinate data type.

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: invalid input syntax for type double precision:

2
phppostgresql
So what is the value of the string $query which is passed to pg_exec()? – Michael Berkowski
I think for double, you don't need to add quotes (") when you specify the values in your query. – Yohanes Khosiawan 许先汉

2 Answers

3
votes

Manual quoting should be avoided, since it's both error prone and subject to potential injection attacks.

Most SQL libraries have a function which takes parameters, and uses placeholders in the query. The library takes care of the quoting, and prevents injection points.

Your best approach for this code is probably to use pg_query_params, which allows you to pass the parameters as an array, and takes care of escaping for you. You use $1, $2, etc. in the query as placeholders.

So basically your VALUES clause would be replaced by ($1, $2, $3...) and so on, with the values being passed to pg_query_params instead of interpolated directly.

2
votes

Combined with what @khampson says, I think your core issue is that you're quoting your latitude and longitude fields, which I am guessing are NOT strings but are doubles with precision. But you're parsing them as strings from the PHP $_POST and building your SQL query as if they were strings.

So you should do two things:

  1. Use pg_query_params
  2. When you build your array of values to pass to pg_query_params ensure you cast those values to a float. This is so that pg_query_params can have appropriate knowledge of the type so that it knows NOT to quote it as a string but to treat it as a raw numeric type.

Micro example:

$latitude = $_POST['latitude'];
$longitude = $_POST['longitude'];

$params = array(floatval($latitude), floatval($longitude));

$query = "INSERT INTO table (latitude, longitude) VALUES ($1, $2)";
pg_query_params($connection, $query, params);