Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[dev.icinga.com #5577] PostgreSQL string escaping #1206

Closed
icinga-migration opened this issue Jan 29, 2014 · 11 comments · Fixed by #8955
Closed

[dev.icinga.com #5577] PostgreSQL string escaping #1206

icinga-migration opened this issue Jan 29, 2014 · 11 comments · Fixed by #8955
Labels
area/db-ido Database output bug Something isn't working
Milestone
2.0.1

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/5577

Created by mfriedrich on 2014-01-29 13:52:50 +00:00

Assignee: gbeutner
Status: Resolved (closed on 2014-06-25 07:50:04 +00:00)
Target Version: 2.0.1
Last Update: 2014-09-16 09:21:33 +00:00 (in Redmine)

Icinga Version: 0.0.6

WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...e, command_type, entry_time, instance_id) VALUES ('moo-ng;Te...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

Changesets

2014-06-25 07:47:20 +00:00 by gbeutner ac6f3f8

Fix PostgreSQL warnings about escaped strings

fixes #5577
@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-01-29 14:19:32 +00:00

  • Subject changed from PostgreSQL string quoting to PostgreSQL string escaping

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-03-18 16:23:32 +00:00

  • Target Version set to 0.0.10

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-04-29 12:14:07 +00:00

  • Target Version changed from 0.0.10 to 0.0.11

@icinga-migration
Copy link
Author

Updated by gbeutner on 2014-05-16 09:45:32 +00:00

  • Target Version changed from 0.0.11 to 2.0 Beta 1

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-05-21 13:31:24 +00:00

  • Target Version changed from 2.0 Beta 1 to 186

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-06-06 09:01:35 +00:00

  • Target Version deleted 186

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-06-17 16:18:16 +00:00

  • Target Version set to 2.0.1

@icinga-migration
Copy link
Author

Updated by gbeutner on 2014-06-25 07:48:18 +00:00

Hm, I'm not quite sure why we didn't just prefix all strings with E. Anyway, this seems to work with PostgreSQL 9.1 and 8.x is (almost) EOL anyway.

@icinga-migration
Copy link
Author

Updated by gbeutner on 2014-06-25 07:48:23 +00:00

  • Assigned to set to gbeutner

@icinga-migration
Copy link
Author

Updated by gbeutner on 2014-06-25 07:50:04 +00:00

  • Status changed from New to Resolved
  • Done % changed from 0 to 100

Applied in changeset i2:ac6f3f8acf4f52adedc7908db6452ed6da273ad7.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-09-16 09:21:33 +00:00

  • Project changed from 32 to Icinga 2
  • Category changed from 163 to DB IDO

@icinga-migration icinga-migration added Low bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.0.1 milestone Jan 17, 2017
julianbrost added a commit that referenced this issue Aug 5, 2021
@julianbrost
IDO PgSQL: always use regular string literals
d4d4fbb 
IdoPgsqlConnection::Escape() internally uses PQescapeStringConn() and its
documentation states the following:

  Furthermore, PQescapeStringConn does not generate the single quotes that must
  surround PostgreSQL string literals; they should be provided in the SQL
  command that the result is inserted into.

So it's intended to use the result in 'string' literals, not in E'string'
literals as Icinga did. This results in problems as the behavior of
PQescapeStringConn() depends on how the current connection will interpret
regular single quoted literals, namely on the value of the
standard_conforming_strings variable.

The E'string' literals were initally introduced in
ac6f3f8 to fix #1206 where PostgreSQL started
warning about escape sequences in string litereals not supported by the SQL
standard (but by PostgreSQL depending on the value of
standard_conforming_strings). In the meantime the oldest PostgreSQL version on
any platform supported by Icinga increased to 9.2 (CentOS 7) and starting with
9.1, standard_conforming_strings is enabled by default, so there will be no
warnings about escape sequences (as the warning is only issued if the escape
sequence is actually interpreted by PostgreSQL).
julianbrost added a commit that referenced this issue Aug 5, 2021
@julianbrost
IDO PgSQL: always use regular string literals
782669f 
IdoPgsqlConnection::Escape() internally uses PQescapeStringConn() and its
documentation states the following:

  Furthermore, PQescapeStringConn does not generate the single quotes that must
  surround PostgreSQL string literals; they should be provided in the SQL
  command that the result is inserted into.

So it's intended to use the result in 'string' literals, not in E'string'
literals as Icinga did. This results in problems as the behavior of
PQescapeStringConn() depends on how the current connection will interpret
regular single quoted literals, namely on the value of the
standard_conforming_strings variable.

The E'string' literals were initially introduced in
ac6f3f8 to fix #1206 where PostgreSQL started
warning about escape sequences in string literals not supported by the SQL
standard (but by PostgreSQL depending on the value of
standard_conforming_strings). In the meantime the oldest PostgreSQL version on
any platform supported by Icinga increased to 9.2 (CentOS 7) and starting with
9.1, standard_conforming_strings is enabled by default, so there will be no
warnings about escape sequences (as the warning is only issued if the escape
sequence is actually interpreted by PostgreSQL).
@julianbrost julianbrost mentioned this issue Aug 5, 2021
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/db-ido Database output bug Something isn't working
Projects
None yet
Milestone
2.0.1
Development

Successfully merging a pull request may close this issue.

IDO PgSQL: always use regular string literals Icinga/icinga2
1 participant
@icinga-migration