duan7264
2012-03-06 18:34
浏览 289
已采纳

将特殊字符插入Postgresql数据库

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();

?>
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douzhao6584 2012-03-06 18:38
    已采纳

    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.

    已采纳该答案
    打赏 评论
  • doushi7761 2012-03-06 20:03

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

    打赏 评论

相关推荐 更多相似问题