[dev.icinga.com #1974] change standard string escaping for postgresql queries to use the E'foo' notation

[icinga-migration]

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

Created by mfriedrich on 2011-10-03 07:25:45 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-03-28 10:35:22 +00:00)
Target Version: 1.7
Last Update: 2014-12-08 14:35:57 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

---

this can be done otherwise, but needs further investigation how this works out with libdbi.

HINT:  Use '' to write quotes in strings, or use the escape string syntax (E'...').
WARNING:  nonstandard use of \' in a string literal at character 226
HINT:  Use '' to write quotes in strings, or use the escape string syntax (E'...').
WARNING:  nonstandard use of \\ in a string literal at character 125

Attachments

• string_escape_locations.txt mfriedrich - 2012-02-18 15:32:49 +00:00
• 90_disable_standard_conforming_strings.dpatch.txt mfriedrich - 2012-04-12 18:03:44 +00:00

Changesets

2012-02-18 17:23:41 +00:00 by mfriedrich 62344c3

idoutils: change standard string escaping for postgresql queries to use the E'foo' notation #1974

refs #1974

2012-04-28 09:14:04 +00:00 by mfriedrich 6332556

idoutils: change standard string escaping for postgresql queries to use the E'foo' notation #1974

refs #1974

Conflicts:

	Changelog

---

Relations:

• relates #1974

[icinga-migration]

Updated by mfriedrich on 2011-11-09 08:55:18 +00:00

• Status changed from New to Assigned
• Assigned to set to mfriedrich
• Priority changed from Low to High

with postgresql 9.x this will be an error and therefore a significant bug to be resolved. question remains, if it's worth patching libdbi (needs a full rewrite on query creation), or focus on libpq and add parameter bindings to all postgresql queries removing necessary escaping.

[icinga-migration]

Updated by mfriedrich on 2011-12-19 18:37:35 +00:00

• Target Version set to 1.7

[icinga-migration]

Updated by viaLAN on 2012-01-06 17:50:20 +00:00

workaround for posgresql 9.x users:
in posgresql.conf set

standard_conforming_strings = off

otherwise all INSERT- and UPDATE-statements containing single quote lead to errors

[icinga-migration]

Updated by mfriedrich on 2012-02-18 15:32:49 +00:00

• File added string_escape_locations.txt

[icinga-migration]

Updated by mfriedrich on 2012-02-18 17:14:49 +00:00

• Subject changed from standard string escaping with postgresql to fix standard string escaping with postgresql to use the E'foo' notation

[icinga-migration]

Updated by mfriedrich on 2012-02-18 17:16:02 +00:00

• Subject changed from fix standard string escaping with postgresql to use the E'foo' notation to change standard string escaping for postgresql queries to use the E'foo' notation

[icinga-migration]

Updated by mfriedrich on 2012-02-18 17:17:44 +00:00

a final query looks like this

Sat Feb 18 18:14:59 2012 .289392 [002.0] [pid=17905] [tid=139651797702400] UPDATE icinga_services SET host_object_id=1288, display_name=E'ok_07', check_command_object_id=1241, check_command_args=E'ok', eventhandler_command_object_id=0, eventhandler_command_args=E'', check_timeperiod_object_id=2, notification_timeperiod_object_id=2, failure_prediction_options=E'', check_interval=1.000000, retry_interval=1.000000, max_check_attempts=3, first_notification_delay=0.000000, notification_interval=0.000000, notify_on_warning=1, notify_on_unknown=1, notify_on_critical=1, notify_on_recovery=1, notify_on_flapping=1, notify_on_downtime=0, stalk_on_ok=0, stalk_on_warning=0, stalk_on_unknown=0, stalk_on_critical=0, is_volatile=0, flap_detection_enabled=1, flap_detection_on_ok=1, flap_detection_on_warning=1, flap_detection_on_unknown=1, flap_detection_on_critical=1, low_flap_threshold=0.000000, high_flap_threshold=0.000000, process_performance_data=1, freshness_checks_enabled=0, freshness_threshold=0, passive_checks_enabled=1, event_handler_enabled=1, active_checks_enabled=1, retain_status_information=1, retain_nonstatus_information=1, notifications_enabled=1, obsess_over_service=1, failure_prediction_enabled=1, notes=E'', notes_url=E'', action_url=E'', icon_image=E'', icon_image_alt=E'' WHERE instance_id=1 AND config_type=1 AND service_object_id=1842

[icinga-migration]

Updated by mfriedrich on 2012-02-18 18:14:14 +00:00

• Done % changed from 0 to 90

test config

define host{
        use                     linux-server            ; Name of host template to use
                                                        ; This host definition will inherit all variables that are defined
                                                        ; in (or inherited by) the linux-server host template definition.
        host_name               1974_localhost
        alias                   1974_localhost
        display_name            1974_localhost
        address                 127.0.0.1
        action_url              http://wiki/.../SvcView?hostgroup=$HOSTGROUPNAME$&hostname=$HOSTNAME$
        check_command           print_escape_string
        }
define command {
        command_name            print_escape_string
        command_line            $USER1$/check_dummy 2 "abc \\c\widoze's\foo&"
}

=> reschedule a new check to trigger the output to be inserted into the db

without patch, postgresql 9.1 log

2012-02-18 18:34:45 CET FEHLER:  Syntaxfehler bei »s« bei Zeichen 84
2012-02-18 18:34:45 CET ANWEISUNG:  UPDATE icinga_commands SET command_line='$USER1$/check_dummy 2 "abc \\\\c\\widoze\'s\\foo&"' WHERE instance_id=1 AND object_id=2280 AND config_type=1

with patch

empty logs, data fully within database.

icinga=# select * from icinga_commands;

        129 |           1 |           1 |      2280 | $USER1$/check_dummy 2 "abc \\c\widoze's\foo&"

[icinga-migration]

Updated by mfriedrich on 2012-03-04 12:40:14 +00:00

• Status changed from Assigned to Feedback
• Done % changed from 90 to 100

[icinga-migration]

Updated by mfriedrich on 2012-03-04 12:40:24 +00:00

• Category set to 58

[icinga-migration]

Updated by mfriedrich on 2012-03-28 10:35:22 +00:00

• Status changed from Feedback to Resolved

[icinga-migration]

Updated by mfriedrich on 2012-04-12 18:03:44 +00:00

• File added 90_disable_standard_conforming_strings.dpatch.txt

for 1.6.x a workaround by formorer

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:35:57 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 58 to IDOUtils
• Icinga Version set to 1
• OS Version set to any