insert special characters into a Postgresql database

Question

I have a php script which returns some values with special characters, especially single quotes(') and the 'at sign'(@). The values that contain these characters are not inserted into the database. I saw a post in doing this on mysql database at (http://stackoverflow.com/questions/2584066/php-how-to-insert-special-characters-into-a-database). My question then is how can it be done in Postgresql database.

See below the php code:

<?php
require 'table.php';


// Opens a connection to a PostgresSQL server
$connection = pg_connect("dbname=postgis user=postgres password=local");


// Execute query        

foreach ($xml->item as $entry){ 
$georss = $entry->children($namespaces['georss']);
list($lat, $lng) = explode(' ', (string)$georss->point);
$query = "INSERT INTO geognews(title, link, author, latitude, longitude) VALUES ('" . $entry->title . "', '" . $entry->link . "', '" . $entry->children($namespaces['dc'])->creator . "', '" . $lat . "', '" . $lng . "')";

$result = pg_query($query);
printf ("These values are inserted into the database - %s %s %s", $entry->title, $entry->link, $entry->children($namespaces['dc'])->creator, $lat, $lng);
}

pg_close();

?>

Answer 1

Use pg_query_params(), by far the easiest way to avoid SQL injection. And thus quotes ' and other risky stuff.

Answer 2

You have a few of options:

• You could wrap dynamic data pg_escape_string() (and related functions for other types) to properly encode special characters. This will require the least amount of change to the code you've posted.

• You could use prepared statements and bind your dynamic data as parameters. See the docs for pg_prepare() for examples on how to do this. Prepared statements are the recommended way to protect against SQL Injection.

• You could use PDO with parameterized queries. This gives you the safety and performance benefits of parameterized queries plus a universal database abstraction layer.

The last option is preferred.