libdd_trace_obfuscation/
sql.rs

1// Copyright 2023-Present Datadog, Inc. https://www.datadoghq.com/
2// SPDX-License-Identifier: Apache-2.0
3
4fn is_splitter(b: u8) -> bool {
5    matches!(
6        b,
7        b',' | b'(' | b')' | b'|' | b' ' | b'\t' | b'\n' | b'\r' |
8        0xB | // vertical tab 
9        0xC // form feed
10    )
11}
12
13fn is_numeric_litteral_prefix(bytes: &[u8], start: usize) -> bool {
14    matches!(
15        bytes[start],
16        b'0' | b'1' | b'2' | b'3' | b'4' | b'5' | b'6' | b'7' | b'8' | b'9' | b'-' | b'+' | b'.'
17    ) && !(start + 1 < bytes.len() && bytes[start] == b'-' && bytes[start + 1] == b'-')
18}
19
20fn is_hex_litteral_prefix(bytes: &[u8], start: usize, end: usize) -> bool {
21    (bytes[start] | b' ') == b'x' && start + 1 < end && bytes[start + 1] == b'\''
22}
23
24fn is_quoted(bytes: &[u8], start: usize, end: usize) -> bool {
25    bytes[start] == b'\'' && bytes[end - 1] == b'\''
26}
27
28/// Obfuscates an sql string by replacing litterals with '?' chars.
29///
30/// The algorithm works by finding the places where a litteral could start (so called splitters)
31/// and then identifies them by looking at their first few characters.
32///
33/// It does not attempt at rigorous parsing of the SQL syntax, and does not take any context
34/// sensitive decision, contrary to the more exhaustive datadog-agent implementation.
35///
36/// based off
37/// https://github.com/DataDog/dd-trace-java/blob/36e924eaa/internal-api/src/main/java/datadog/trace/api/normalize/SQLNormalizer.java
38pub fn obfuscate_sql_string(s: &str) -> String {
39    let bytes = s.as_bytes();
40    let mut obfuscated = String::new();
41    if s.is_empty() {
42        return obfuscated;
43    }
44    let mut start = 0;
45    loop {
46        if start >= s.len() {
47            break;
48        }
49        let end = next_splitter(bytes, start).unwrap_or(s.len());
50        #[allow(clippy::comparison_chain)]
51        if start + 1 == end {
52            // if the gap is 1 character it can only be a number
53            if bytes[start].is_ascii_digit() {
54                obfuscated.push('?');
55            } else {
56                obfuscated.push_str(&s[start..end]);
57            }
58        } else if start + 1 < end {
59            if is_numeric_litteral_prefix(bytes, start)
60                || is_quoted(bytes, start, end)
61                || is_hex_litteral_prefix(bytes, start, end)
62            {
63                obfuscated.push('?');
64            } else {
65                obfuscated.push_str(&s[start..end]);
66            }
67        }
68        if end < s.len() {
69            obfuscated.push(bytes[end] as char);
70        }
71        start = end + 1;
72    }
73    obfuscated
74}
75
76fn next_splitter(s: &[u8], at: usize) -> Option<usize> {
77    let mut quoted = false;
78    let mut escaped = false;
79    for (pos, b) in s.iter().copied().enumerate().skip(at) {
80        if b == b'\'' && !escaped {
81            quoted = !quoted;
82            continue;
83        }
84        escaped = (b == b'\\') && !escaped;
85        if !quoted && is_splitter(b) {
86            return Some(pos);
87        }
88    }
89    None
90}
91
92#[cfg(test)]
93mod tests {
94
95    #[test]
96    fn test_sql_obfuscation() {
97        let mut panic = None;
98        let err = CASES
99            .iter()
100            .enumerate()
101            .filter_map(|(i, (input, output))| {
102                let err =
103                    match std::panic::catch_unwind(|| test_sql_obfuscation_case(input, output)) {
104                        Ok(r) => r,
105                        Err(p) => {
106                            panic = Some(p);
107                            eprintln!("panicked case {i}\n\tinput: {input}\n\n");
108                            return None;
109                        }
110                    }
111                    .err()?;
112                Some(format!("failed case {i}\n\terr: {err}\n"))
113            })
114            .collect::<String>();
115        if !err.is_empty() {
116            if panic.is_none() {
117                panic!("{err}")
118            } else {
119                eprintln!("{err}")
120            }
121        }
122        if let Some(p) = panic {
123            std::panic::resume_unwind(p);
124        }
125    }
126
127    fn test_sql_obfuscation_case(input: &str, output: &str) -> anyhow::Result<()> {
128        let got = super::obfuscate_sql_string(input);
129        if output != got {
130            anyhow::bail!("expected {output}\n\tgot: {got}")
131        }
132        Ok(())
133    }
134
135    const CASES: &[(&str, &str)] = &[
136        ("" , ""),
137        ("   " , "   "),
138        ("         " , "         "),
139        ("罿" , "罿"),
140        ("罿潯" , "罿潯"),
141        ("罿潯罿潯罿潯罿潯罿潯" , "罿潯罿潯罿潯罿潯罿潯"),
142        ("'abc1287681964'", "?"),
143        ("-- comment", "-- comment"),
144        ("---", "---"),
145        ("1 - 2", "? - ?"),
146        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
147        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
148        ("SELECT * FROM TABLE WHERE userId = '\\'abc1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
149        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964\\''" , "SELECT * FROM TABLE WHERE userId = ?"),
150        ("SELECT * FROM TABLE WHERE userId = '\\'abc1287681964\\''" , "SELECT * FROM TABLE WHERE userId = ?"),
151        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287681\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
152        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
153        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'\\'\\'\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
154        ("SELECT * FROM TABLE WHERE userId IN (\'a\', \'b\', \'c\')" , "SELECT * FROM TABLE WHERE userId IN (?, ?, ?)"),
155        ("SELECT * FROM TABLE WHERE userId IN (\'abc\\'1287681\\'964\', \'abc\\'1287\\'681\\'\\'\\'\\'964\', \'abc\\'1287\\'681\\'964\')" , "SELECT * FROM TABLE WHERE userId IN (?, ?, ?)"),
156        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964' ORDER BY FOO DESC" , "SELECT * FROM TABLE WHERE userId = ? ORDER BY FOO DESC"),
157        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'\\'\\'\\'964' ORDER BY FOO DESC" , "SELECT * FROM TABLE WHERE userId = ? ORDER BY FOO DESC"),
158        ("SELECT * FROM TABLE JOIN SOMETHING ON TABLE.foo = SOMETHING.bar" , "SELECT * FROM TABLE JOIN SOMETHING ON TABLE.foo = SOMETHING.bar"),
159        ("CREATE TABLE \"VALUE\"" , "CREATE TABLE \"VALUE\""),
160        ("INSERT INTO \"VALUE\" (\"column\") VALUES (\'ljahklshdlKASH\')" , "INSERT INTO \"VALUE\" (\"column\") VALUES (?)"),
161        ("INSERT INTO \"VALUE\" (\"col1\",\"col2\",\"col3\") VALUES (\'blah\',12983,X'ff')" , "INSERT INTO \"VALUE\" (\"col1\",\"col2\",\"col3\") VALUES (?,?,?)"),
162        ("INSERT INTO \"VALUE\" (\"col1\", \"col2\", \"col3\") VALUES (\'blah\',12983,X'ff')" , "INSERT INTO \"VALUE\" (\"col1\", \"col2\", \"col3\") VALUES (?,?,?)"),
163        ("INSERT INTO VALUE (col1,col2,col3) VALUES (\'blah\',12983,X'ff')" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
164        ("INSERT INTO VALUE (col1,col2,col3) VALUES (12983,X'ff',\'blah\')" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
165        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'blah\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
166        ("INSERT INTO VALUE (col1,col2,col3) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
167        ("INSERT INTO VALUE (col1, col2, col3) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE (col1, col2, col3) VALUES (?,?,?)"),
168        ("INSERT INTO VALUE ( col1, col2, col3 ) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE ( col1, col2, col3 ) VALUES (?,?,?)"),
169        ("INSERT INTO VALUE (col1,col2,col3) VALUES ('a', \'b\' ,1)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?, ? ,?)"),
170        ("INSERT INTO VALUE (col1, col2, col3) VALUES ('a', \'b\', 1)" , "INSERT INTO VALUE (col1, col2, col3) VALUES (?, ?, ?)"),
171        ("INSERT INTO VALUE ( col1, col2, col3 ) VALUES ('a', \'b\', 1)" , "INSERT INTO VALUE ( col1, col2, col3 ) VALUES (?, ?, ?)"),
172        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'罿潯罿潯罿潯罿潯罿潯\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
173        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'罿\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
174        ("SELECT 3 AS NUCLEUS_TYPE,A0.ID,A0.\"NAME\" FROM \"VALUE\" A0" , "SELECT ? AS NUCLEUS_TYPE,A0.ID,A0.\"NAME\" FROM \"VALUE\" A0"),
175        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > .9999" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
176        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > 0.9999" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
177        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > -0.9999" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
178        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > -1e6" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
179        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > +1e6" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
180        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > +255" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
181        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > +6.34F" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
182        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > +6f" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
183        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > +0.5D" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
184        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > -1d" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
185        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > x'ff'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
186        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > X'ff'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
187        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > 0xff" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 > ?"),
188        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \'\'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
189        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
190        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \'  \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
191        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' \\\' \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
192        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' \\\'Бегите, глупцы \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
193        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' x \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
194        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' x x\'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
195        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' x\\\'ab x\'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
196        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \' \\\' 0xf \'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
197        ("SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> \'5,123\'" , "SELECT COUNT(*) FROM TABLE_1 JOIN table_2 ON TABLE_1.foo = table_2.bar where col1 <> ?"),
198        ("CREATE TABLE S_H2 (id INTEGER not NULL, PRIMARY KEY ( id ))" , "CREATE TABLE S_H2 (id INTEGER not NULL, PRIMARY KEY ( id ))"),
199        ("CREATE TABLE S_H2 ( id INTEGER not NULL, PRIMARY KEY ( id ) )" , "CREATE TABLE S_H2 ( id INTEGER not NULL, PRIMARY KEY ( id ) )"),
200        ("SELECT * FROM TABLE WHERE name = 'O''Brady'" , "SELECT * FROM TABLE WHERE name = ?"),
201        ("INSERT INTO visits VALUES (2, 8, '2013-01-02', 'rabies shot')" , "INSERT INTO visits VALUES (?, ?, ?, ?)"),
202        (
203            "SELECT
204\tcountry.country_name_eng,
205\tSUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls,
206\tAVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_difference
207FROM country
208LEFT JOIN city ON city.country_id = country.id
209LEFT JOIN customer ON city.id = customer.city_id
210LEFT JOIN call ON call.customer_id = customer.id
211GROUP BY
212\tcountry.id,
213\tcountry.country_name_eng
214HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
215ORDER BY calls DESC, country.id ASC;",
216                "SELECT
217\tcountry.country_name_eng,
218\tSUM(CASE WHEN call.id IS NOT NULL THEN ? ELSE ? END) AS calls,
219\tAVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),?)) AS avg_difference
220FROM country
221LEFT JOIN city ON city.country_id = country.id
222LEFT JOIN customer ON city.id = customer.city_id
223LEFT JOIN call ON call.customer_id = customer.id
224GROUP BY
225\tcountry.id,
226\tcountry.country_name_eng
227HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),?)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
228ORDER BY calls DESC, country.id ASC;"
229        ),
230        ("DROP VIEW IF EXISTS v_country_all; GO CREATE VIEW v_country_all AS SELECT * FROM country;", "DROP VIEW IF EXISTS v_country_all; GO CREATE VIEW v_country_all AS SELECT * FROM country;"),
231        (
232            "UPDATE v_country_all /* 1. in-line comment */ SET
233/*
234    * 2. multi-line comment
235    */
236country_name = 'Nova1'
237-- 3. single-line comment
238WHERE id = 8;",
239                "UPDATE v_country_all /* ? in-line comment */ SET
240/*
241    * ? multi-line comment
242    */
243country_name = ?
244-- ? single-line comment
245WHERE id = ?"
246        ),
247        (
248            "INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Deutschland', 'Germany', 'DEU');
249INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Srbija', 'Serbia', 'SRB');
250INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Hrvatska', 'Croatia', 'HRV');
251INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('United States of America', 'United States of America', 'USA');
252INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Polska', 'Poland', 'POL');",
253                "INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
254INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
255INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
256INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
257INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);"
258        ),
259        ("SELECT * FROM TABLE WHERE userId = ',' and foo=foo.bar", "SELECT * FROM TABLE WHERE userId = ? and foo=foo.bar"),
260        ("SELECT * FROM TABLE WHERE userId =     ','||foo.bar", "SELECT * FROM TABLE WHERE userId =     ?||foo.bar"),
261        (
262            concat!(
263            "SELECT count(*) AS totcount FROM (SELECT \"c1\", \"c2\",\"c3\",\"c4\",\"c5\",\"c6\",\"c7\",\"c8\", \"c9\", \"c10\",\"c11\",\"c12\",\"c13\",\"c14\", \"c15\",\"c16\",\"c17\",\"c18\", \"c19\",\"c20\",\"c21\",\"c22\",\"c23\", \"c24\",\"c25\",\"c26\", \"c27\" FROM (SELECT bar.y AS \"c2\", foo.x AS \"c3\", foo.z AS \"c4\", DECODE(foo.a, NULL,NULL, foo.a ||', '|| foo.b) AS \"c5\" , foo.c AS \"c6\", bar.d AS \"c1\", bar.e AS \"c7\", bar.f AS \"c8\", bar.g AS \"c9\", TO_DATE(TO_CHAR(TO_DATE(bar.h,'YYYYMMDD'),'DD-MON-YYYY'),'DD-MON-YYYY') AS \"c10\", TO_DATE(TO_CHAR(TO_DATE(bar.i,'YYYYMMDD'),'DD-MON-YYYY'),'DD-MON-YYYY') AS \"c11\", CASE WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 150 THEN '>150 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 120 THEN '121 to 150 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 90 THEN '91 to 120 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 60 THEN '61 to 90 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 30 THEN '31 to 60 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 0 THEN '1 to 30 Days' ELSE NULL END AS \"c12\", DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD')),NULL) as \"c13\", bar.k AS \"c14\", bar.l ||', '||bar.m AS \"c15\", DECODE(bar.n, NULL, NULL,bar.n ||', '||bar.o) AS \"c16\", bar.p AS \"c17\", bar.q AS \"c18\", bar.r AS \"c19\", bar.s AS \"c20\", qux.a AS \"c21\", TO_CHAR(TO_DATE(qux.b,'YYYYMMDD'),'DD-MON-YYYY') AS \"c22\", DECODE(qux.l,NULL,NULL, qux.l ||', '||qux.m) AS \"c23\", bar.a AS \"c24\", TO_CHAR(TO_DATE(bar.j,'YYYYMMDD'),'DD-MON-YYYY') AS \"c25\", DECODE(bar.c , 1,'N',0, 'Y', bar.c ) AS \"c26\", bar.y AS y, bar.d, bar.d AS \"c27\" FROM blort.bar , ( SELECT * FROM (SELECT a,a,l,m,b,c, RANK() OVER (PARTITION BY c ORDER BY b DESC) RNK FROM blort.d WHERE y IN (:protocols)) WHERE RNK = 1) qux, blort.foo WHERE bar.c = qux.c(+) AND bar.x = foo.x AND bar.y IN (:protocols) and bar.x IN (:sites)) ) ",
264            "SELECT count(*) AS totcount FROM (SELECT \"c1\", \"c2\",\"c3\",\"c4\",\"c5\",\"c6\",\"c7\",\"c8\", \"c9\", \"c10\",\"c11\",\"c12\",\"c13\",\"c14\", \"c15\",\"c16\",\"c17\",\"c18\", \"c19\",\"c20\",\"c21\",\"c22\",\"c23\", \"c24\",\"c25\",\"c26\", \"c27\" FROM (SELECT bar.y AS \"c2\", foo.x AS \"c3\", foo.z AS \"c4\", DECODE(foo.a, NULL,NULL, foo.a ||', '|| foo.b) AS \"c5\" , foo.c AS \"c6\", bar.d AS \"c1\", bar.e AS \"c7\", bar.f AS \"c8\", bar.g AS \"c9\", TO_DATE(TO_CHAR(TO_DATE(bar.h,'YYYYMMDD'),'DD-MON-YYYY'),'DD-MON-YYYY') AS \"c10\", TO_DATE(TO_CHAR(TO_DATE(bar.i,'YYYYMMDD'),'DD-MON-YYYY'),'DD-MON-YYYY') AS \"c11\", CASE WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 150 THEN '>150 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 120 THEN '121 to 150 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 90 THEN '91 to 120 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 60 THEN '61 to 90 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 30 THEN '31 to 60 Days' WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD'))) > 0 THEN '1 to 30 Days' ELSE NULL END AS \"c12\", DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,'YYYYMMDD')),NULL) as \"c13\", bar.k AS \"c14\", bar.l ||', '||bar.m AS \"c15\", DECODE(bar.n, NULL, NULL,bar.n ||', '||bar.o) AS \"c16\", bar.p AS \"c17\", bar.q AS \"c18\", bar.r AS \"c19\", bar.s AS \"c20\", qux.a AS \"c21\", TO_CHAR(TO_DATE(qux.b,'YYYYMMDD'),'DD-MON-YYYY') AS \"c22\", DECODE(qux.l,NULL,NULL, qux.l ||', '||qux.m) AS \"c23\", bar.a AS \"c24\", TO_CHAR(TO_DATE(bar.j,'YYYYMMDD'),'DD-MON-YYYY') AS \"c25\", DECODE(bar.c , 1,'N',0, 'Y', bar.c ) AS \"c26\", bar.y AS y, bar.d, bar.d AS \"c27\" FROM blort.bar , ( SELECT * FROM (SELECT a,a,l,m,b,c, RANK() OVER (PARTITION BY c ORDER BY b DESC) RNK FROM blort.d WHERE y IN (:protocols)) WHERE RNK = 1) qux, blort.foo WHERE bar.c = qux.c(+) AND bar.x = foo.x AND bar.y IN (:protocols) and bar.x IN (:sites)) )"),
265            concat!(
266                "SELECT count(*) AS totcount FROM (SELECT \"c1\", \"c2\",\"c3\",\"c4\",\"c5\",\"c6\",\"c7\",\"c8\", \"c9\", \"c10\",\"c11\",\"c12\",\"c13\",\"c14\", \"c15\",\"c16\",\"c17\",\"c18\", \"c19\",\"c20\",\"c21\",\"c22\",\"c23\", \"c24\",\"c25\",\"c26\", \"c27\" FROM (SELECT bar.y AS \"c2\", foo.x AS \"c3\", foo.z AS \"c4\", DECODE(foo.a, NULL,NULL, foo.a ||?|| foo.b) AS \"c5\" , foo.c AS \"c6\", bar.d AS \"c1\", bar.e AS \"c7\", bar.f AS \"c8\", bar.g AS \"c9\", TO_DATE(TO_CHAR(TO_DATE(bar.h,?),?),?) AS \"c10\", TO_DATE(TO_CHAR(TO_DATE(bar.i,?),?),?) AS \"c11\", CASE WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? ELSE NULL END AS \"c12\", DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?)),NULL) as \"c13\", bar.k AS \"c14\", bar.l ||?||bar.m AS \"c15\", DECODE(bar.n, NULL, NULL,bar.n ||?||bar.o) AS \"c16\", bar.p AS \"c17\", bar.q AS \"c18\", bar.r AS \"c19\", bar.s AS \"c20\", qux.a AS \"c21\", TO_CHAR(TO_DATE(qux.b,?),?) AS \"c22\", DECODE(qux.l,NULL,NULL, qux.l ||?||qux.m) AS \"c23\", bar.a AS \"c24\", TO_CHAR(TO_DATE(bar.j,?),?) AS \"c25\", DECODE(bar.c , ?,?,?, ?, bar.c ) AS \"c26\", bar.y AS y, bar.d, bar.d AS \"c27\" FROM blort.bar , ( SELECT * FROM (SELECT a,a,l,m,b,c, RANK() OVER (PARTITION BY c ORDER BY b DESC) RNK FROM blort.d WHERE y IN (:protocols)) WHERE RNK = ?) qux, blort.foo WHERE bar.c = qux.c(+) AND bar.x = foo.x AND bar.y IN (:protocols) and bar.x IN (:sites)) ) ",
267                "SELECT count(*) AS totcount FROM (SELECT \"c1\", \"c2\",\"c3\",\"c4\",\"c5\",\"c6\",\"c7\",\"c8\", \"c9\", \"c10\",\"c11\",\"c12\",\"c13\",\"c14\", \"c15\",\"c16\",\"c17\",\"c18\", \"c19\",\"c20\",\"c21\",\"c22\",\"c23\", \"c24\",\"c25\",\"c26\", \"c27\" FROM (SELECT bar.y AS \"c2\", foo.x AS \"c3\", foo.z AS \"c4\", DECODE(foo.a, NULL,NULL, foo.a ||?|| foo.b) AS \"c5\" , foo.c AS \"c6\", bar.d AS \"c1\", bar.e AS \"c7\", bar.f AS \"c8\", bar.g AS \"c9\", TO_DATE(TO_CHAR(TO_DATE(bar.h,?),?),?) AS \"c10\", TO_DATE(TO_CHAR(TO_DATE(bar.i,?),?),?) AS \"c11\", CASE WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? WHEN DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?))) > ? THEN ? ELSE NULL END AS \"c12\", DECODE(bar.j, NULL, TRUNC(SYSDATE) - TRUNC(TO_DATE(bar.h,?)),NULL) as \"c13\", bar.k AS \"c14\", bar.l ||?||bar.m AS \"c15\", DECODE(bar.n, NULL, NULL,bar.n ||?||bar.o) AS \"c16\", bar.p AS \"c17\", bar.q AS \"c18\", bar.r AS \"c19\", bar.s AS \"c20\", qux.a AS \"c21\", TO_CHAR(TO_DATE(qux.b,?),?) AS \"c22\", DECODE(qux.l,NULL,NULL, qux.l ||?||qux.m) AS \"c23\", bar.a AS \"c24\", TO_CHAR(TO_DATE(bar.j,?),?) AS \"c25\", DECODE(bar.c , ?,?,?, ?, bar.c ) AS \"c26\", bar.y AS y, bar.d, bar.d AS \"c27\" FROM blort.bar , ( SELECT * FROM (SELECT a,a,l,m,b,c, RANK() OVER (PARTITION BY c ORDER BY b DESC) RNK FROM blort.d WHERE y IN (:protocols)) WHERE RNK = ?) qux, blort.foo WHERE bar.c = qux.c(+) AND bar.x = foo.x AND bar.y IN (:protocols) and bar.x IN (:sites)) )"
268            )
269        ),
270    ];
271}
libdd_trace_obfuscation/sql.rs

libdd_trace_obfuscation/
sql.rs
