1

I have a query where I get the error:

The code is:

$prefix = $data->sheets[0]['cells'][$row][2];
$fiyat = $data->sheets[0]['cells'][$row][3];
$increment = $data->sheets[0]['cells'][$row][4];
$tarih = $data->sheets[0]['cells'][$row][5];
$tarih = explode('.',$tarih);
$effective_date = $tarih[2] . "-" . $tarih[1] . "-" . $tarih[0];

All variables returns correct values (and variable types).

$query_insert_to_rates = "INSERT INTO rates VALUES (nextval('routes_seq'), '$prefix', '$i_tariff', '$fiyat_orj', '$fiyat_orj', '$increment', '$increment', '$forbidden', 't', '0', '0', '$increment', '$increment', NULL, '$eff_date_ins', NULL);";

$result_insert_to_rates = pg_query($query_insert_to_rates);

INSERT INTO rates VALUES (nextval('routes_seq'), '21321', '8', '0.0470', '0.0470', '1', '1', 't', 't', '0', '0', '1', '1', NULL, '2012-06-01 00:00:00.000001+02', NULL);

Warning: pg_query() [function.pg-query]: Query failed: ERROR: unterminated quoted string at or near "'2" at character 50 in.....

Now, when I run the query within the PHP, I get the above error. My PHP Version is 5.3.10. And the machine is Centos 5 (VPS).

However if I run the query through Navicat(connected to the DB) then I have no error, and query executed perfectly.

Any help would be appreciated.

DrColossos
  • 12,656
  • 3
  • 46
  • 67
OrhunB
  • 11
  • 1
  • 3
  • Please also post the output of `echo $query_insert_to_rates;`. – Mark Byers Jun 09 '12 at 21:55
  • INSERT INTO rates VALUES (nextval('routes_seq'), '21321', '8', '0.0470', '0.0470', '1', '1', 't', 't', '0', '0', '1', '1', NULL, '2012-06-01 00:00:00.000001+02', NULL); – OrhunB Jun 09 '12 at 22:04
  • 1
    For safety's sake, please use parametrized SQL queries. http://bobby-tables.com/php.html has example code. It should also take care of your problem because the PDO interface library will take care of formatting the SQL in a way that Postgresql wants it. For that matter, you should be using PHP's PDO database abstraction layer, rather than the `pg_query` family of functions. http://php.net/manual/en/book.pdo.php – Andy Lester Jun 09 '12 at 21:31
  • Search in the server logs for the exact SQL text of the failing query. Because according to the error message it is different from what you send. – Daniel Vérité Jun 10 '12 at 12:39

1 Answers1

2

This is terrible code: unsafe and insecure. Don't use this pattern ever

$SQL = "INSERT ... ('$var1', '$var2')

use PDO parametrized queries or pg_escape_string function instead

pg_escape_string

Community
  • 1
  • 1
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • It will work as a local and not open to internet. And also only one person will use it. When i use pg_escape_string: the original number is '7717' and when i use pg_escape_string it becomes '7\0007\0001\0007' which is not making sense. – OrhunB Jun 10 '12 at 10:05
  • you newer know, where and who will be use your application - and who read this thread - I tested pg_escape_string and it works well - probably you have wrong internal PHP type: – Pavel Stehule Jun 10 '12 at 11:41