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/// Returns the index just past the closing `'` of a quoted string starting at `start`,
29/// or None if no complete quoted string starts there.
30fn find_quoted_string_end(bytes: &[u8], start: usize, end: usize) -> Option<usize> {
31    if bytes[start] != b'\'' {
32        return None;
33    }
34    let mut i = start + 1;
35    let mut escaped = false;
36    while i < end {
37        if escaped {
38            escaped = false;
39        } else if bytes[i] == b'\\' {
40            escaped = true;
41        } else if bytes[i] == b'\'' {
42            if i + 1 < end && bytes[i + 1] == b'\'' {
43                i += 1; // double-quote escape ''
44            } else {
45                return Some(i + 1); // past closing quote
46            }
47        }
48        i += 1;
49    }
50    None
51}
52
53/// Obfuscates an sql string by replacing litterals with '?' chars.
54///
55/// The algorithm works by finding the places where a litteral could start (so called splitters)
56/// and then identifies them by looking at their first few characters.
57///
58/// It does not attempt at rigorous parsing of the SQL syntax, and does not take any context
59/// sensitive decision, contrary to the more exhaustive datadog-agent implementation.
60///
61/// based off
62/// https://github.com/DataDog/dd-trace-java/blob/36e924eaa/internal-api/src/main/java/datadog/trace/api/normalize/SQLNormalizer.java
63pub fn obfuscate_sql_string(s: &str) -> String {
64    let bytes = s.as_bytes();
65    let mut obfuscated = String::new();
66    if s.is_empty() {
67        return obfuscated;
68    }
69    let mut start = 0;
70    loop {
71        if start >= s.len() {
72            break;
73        }
74        let end = next_splitter(bytes, start).unwrap_or(s.len());
75        #[allow(clippy::comparison_chain)]
76        if start + 1 == end {
77            // if the gap is 1 character it can only be a number
78            if bytes[start].is_ascii_digit() {
79                obfuscated.push('?');
80            } else {
81                obfuscated.push_str(&s[start..end]);
82            }
83        } else if start + 1 < end {
84            if is_numeric_litteral_prefix(bytes, start)
85                || is_quoted(bytes, start, end)
86                || is_hex_litteral_prefix(bytes, start, end)
87            {
88                obfuscated.push('?');
89            } else if bytes[start] == b'\'' {
90                // Segment starts with a quoted string followed by more content (e.g.
91                // 'value'::type). Obfuscate the quoted part and keep the suffix.
92                if let Some(q_end) = find_quoted_string_end(bytes, start, end) {
93                    obfuscated.push('?');
94                    obfuscated.push_str(&s[q_end..end]);
95                } else {
96                    obfuscated.push_str(&s[start..end]);
97                }
98            } else {
99                obfuscated.push_str(&s[start..end]);
100            }
101        }
102        if end < s.len() {
103            obfuscated.push(bytes[end] as char);
104        }
105        start = end + 1;
106    }
107    obfuscated
108}
109
110/// SQL obfuscation with Go-compatible whitespace normalization for use in JSON plan obfuscation.
111/// Applies obfuscate_sql_string then:
112/// - Strips MySQL backtick identifiers (`id`) and adds spaces around adjacent `.` separators
113/// - Adds a space after `(` and before `)`
114/// - Adds spaces around `::` (PostgreSQL cast operator)
115pub fn obfuscate_sql_string_normalized(s: &str) -> String {
116    let obfuscated = obfuscate_sql_string(s);
117    normalize_plan_sql(&obfuscated)
118}
119
120fn normalize_plan_sql(s: &str) -> String {
121    let mut result = String::with_capacity(s.len());
122    let mut chars = s.chars().peekable();
123
124    while let Some(c) = chars.next() {
125        match c {
126            '`' => {
127                // Strip backticks: collect identifier content up to closing backtick
128                let identifier: String = chars.by_ref().take_while(|&c| c != '`').collect();
129                result.push_str(&identifier);
130
131                // If followed by `.` then another backtick identifier, replace `.` with ` . `
132                if chars.peek() == Some(&'.') {
133                    chars.next(); // consume `.`
134                    result.push_str(if chars.peek() == Some(&'`') {
135                        " . "
136                    } else {
137                        "."
138                    });
139                }
140            }
141            '(' => {
142                result.push('(');
143                if chars.peek().is_some_and(|&c| c != ' ') {
144                    result.push(' ');
145                }
146            }
147            ')' => {
148                if result.as_bytes().last().is_some_and(|&b| b != b' ') {
149                    result.push(' ');
150                }
151                result.push(')');
152            }
153            ':' if chars.peek() == Some(&':') => {
154                chars.next(); // consume second `:`
155                if result.as_bytes().last().is_some_and(|&b| b != b' ') {
156                    result.push(' ');
157                }
158                result.push_str("::");
159                if chars.peek().is_some_and(|&c| c != ' ') {
160                    result.push(' ');
161                }
162            }
163            _ => result.push(c),
164        }
165    }
166    result
167}
168
169fn next_splitter(s: &[u8], at: usize) -> Option<usize> {
170    let mut quoted = false;
171    let mut escaped = false;
172    for (pos, b) in s.iter().copied().enumerate().skip(at) {
173        if b == b'\'' && !escaped {
174            quoted = !quoted;
175            continue;
176        }
177        escaped = (b == b'\\') && !escaped;
178        if !quoted && is_splitter(b) {
179            return Some(pos);
180        }
181    }
182    None
183}
184
185#[cfg(test)]
186mod tests {
187
188    #[test]
189    fn test_sql_obfuscation() {
190        let mut panic = None;
191        let err = CASES
192            .iter()
193            .enumerate()
194            .filter_map(|(i, (input, output))| {
195                let err =
196                    match std::panic::catch_unwind(|| test_sql_obfuscation_case(input, output)) {
197                        Ok(r) => r,
198                        Err(p) => {
199                            panic = Some(p);
200                            eprintln!("panicked case {i}\n\tinput: {input}\n\n");
201                            return None;
202                        }
203                    }
204                    .err()?;
205                Some(format!("failed case {i}\n\terr: {err}\n"))
206            })
207            .collect::<String>();
208        if !err.is_empty() {
209            if panic.is_none() {
210                panic!("{err}")
211            } else {
212                eprintln!("{err}")
213            }
214        }
215        if let Some(p) = panic {
216            std::panic::resume_unwind(p);
217        }
218    }
219
220    fn test_sql_obfuscation_case(input: &str, output: &str) -> anyhow::Result<()> {
221        let got = super::obfuscate_sql_string(input);
222        if output != got {
223            anyhow::bail!("expected {output}\n\tgot: {got}")
224        }
225        Ok(())
226    }
227
228    #[test]
229    fn test_sql_obfuscation_normalized() {
230        let mut panic = None;
231        let err = NORMALIZED_CASES
232            .iter()
233            .enumerate()
234            .filter_map(|(i, (input, output))| {
235                let err = match std::panic::catch_unwind(|| {
236                    test_sql_obfuscation_normalized_case(input, output)
237                }) {
238                    Ok(r) => r,
239                    Err(p) => {
240                        panic = Some(p);
241                        eprintln!("panicked normalized case {i}\n\tinput: {input}\n\n");
242                        return None;
243                    }
244                }
245                .err()?;
246                Some(format!("failed normalized case {i}\n\terr: {err}\n"))
247            })
248            .collect::<String>();
249        if !err.is_empty() {
250            if panic.is_none() {
251                panic!("{err}")
252            } else {
253                eprintln!("{err}")
254            }
255        }
256        if let Some(p) = panic {
257            std::panic::resume_unwind(p);
258        }
259    }
260
261    fn test_sql_obfuscation_normalized_case(input: &str, output: &str) -> anyhow::Result<()> {
262        let got = super::obfuscate_sql_string_normalized(input);
263        if output != got {
264            anyhow::bail!("expected {output}\n\tgot: {got}")
265        }
266        Ok(())
267    }
268
269    const NORMALIZED_CASES: &[(&str, &str)] = &[
270        // 'value'::type fix (in obfuscate_sql_string)
271        ("'60'::double precision", "? :: double precision"),
272        ("'dogfood'::text", "? :: text"),
273        ("'15531'::tid", "? :: tid"),
274        ("(query <> 'dogfood'::text)", "( query <> ? :: text )"),
275        // normalize_plan_sql — parens spacing
276        ("(foo != ?)", "( foo != ? )"),
277        ("((a >= ?) AND (b < ?))", "( ( a >= ? ) AND ( b < ? ) )"),
278        // normalize_plan_sql — :: spacing
279        ("?::double precision", "? :: double precision"),
280        ("(query <> ?::text)", "( query <> ? :: text )"),
281        // normalize_plan_sql — backtick stripping
282        ("`id`", "id"),
283        (
284            "(`sbtest`.`sbtest1`.`id` between ? and ?)",
285            "( sbtest . sbtest1 . id between ? and ? )",
286        ),
287        // full pipeline (obfuscate_sql_string_normalized)
288        (
289            "(`sbtest`.`sbtest1`.`id` between 5016 and 5115)",
290            "( sbtest . sbtest1 . id between ? and ? )",
291        ),
292        ("(query <> 'dogfood'::text)", "( query <> ? :: text )"),
293        ("'60'::double precision", "? :: double precision"),
294    ];
295
296    const CASES: &[(&str, &str)] = &[
297        ("" , ""),
298        ("   " , "   "),
299        ("         " , "         "),
300        ("罿" , "罿"),
301        ("罿潯" , "罿潯"),
302        ("罿潯罿潯罿潯罿潯罿潯" , "罿潯罿潯罿潯罿潯罿潯"),
303        ("'abc1287681964'", "?"),
304        ("-- comment", "-- comment"),
305        ("---", "---"),
306        ("1 - 2", "? - ?"),
307        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
308        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
309        ("SELECT * FROM TABLE WHERE userId = '\\'abc1287681964'" , "SELECT * FROM TABLE WHERE userId = ?"),
310        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964\\''" , "SELECT * FROM TABLE WHERE userId = ?"),
311        ("SELECT * FROM TABLE WHERE userId = '\\'abc1287681964\\''" , "SELECT * FROM TABLE WHERE userId = ?"),
312        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287681\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
313        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
314        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'\\'\\'\\'964'" , "SELECT * FROM TABLE WHERE userId = ?"),
315        ("SELECT * FROM TABLE WHERE userId IN (\'a\', \'b\', \'c\')" , "SELECT * FROM TABLE WHERE userId IN (?, ?, ?)"),
316        ("SELECT * FROM TABLE WHERE userId IN (\'abc\\'1287681\\'964\', \'abc\\'1287\\'681\\'\\'\\'\\'964\', \'abc\\'1287\\'681\\'964\')" , "SELECT * FROM TABLE WHERE userId IN (?, ?, ?)"),
317        ("SELECT * FROM TABLE WHERE userId = 'abc1287681964' ORDER BY FOO DESC" , "SELECT * FROM TABLE WHERE userId = ? ORDER BY FOO DESC"),
318        ("SELECT * FROM TABLE WHERE userId = 'abc\\'1287\\'681\\'\\'\\'\\'964' ORDER BY FOO DESC" , "SELECT * FROM TABLE WHERE userId = ? ORDER BY FOO DESC"),
319        ("SELECT * FROM TABLE JOIN SOMETHING ON TABLE.foo = SOMETHING.bar" , "SELECT * FROM TABLE JOIN SOMETHING ON TABLE.foo = SOMETHING.bar"),
320        ("CREATE TABLE \"VALUE\"" , "CREATE TABLE \"VALUE\""),
321        ("INSERT INTO \"VALUE\" (\"column\") VALUES (\'ljahklshdlKASH\')" , "INSERT INTO \"VALUE\" (\"column\") VALUES (?)"),
322        ("INSERT INTO \"VALUE\" (\"col1\",\"col2\",\"col3\") VALUES (\'blah\',12983,X'ff')" , "INSERT INTO \"VALUE\" (\"col1\",\"col2\",\"col3\") VALUES (?,?,?)"),
323        ("INSERT INTO \"VALUE\" (\"col1\", \"col2\", \"col3\") VALUES (\'blah\',12983,X'ff')" , "INSERT INTO \"VALUE\" (\"col1\", \"col2\", \"col3\") VALUES (?,?,?)"),
324        ("INSERT INTO VALUE (col1,col2,col3) VALUES (\'blah\',12983,X'ff')" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
325        ("INSERT INTO VALUE (col1,col2,col3) VALUES (12983,X'ff',\'blah\')" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
326        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'blah\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
327        ("INSERT INTO VALUE (col1,col2,col3) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
328        ("INSERT INTO VALUE (col1, col2, col3) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE (col1, col2, col3) VALUES (?,?,?)"),
329        ("INSERT INTO VALUE ( col1, col2, col3 ) VALUES ('a',\'b\',1)" , "INSERT INTO VALUE ( col1, col2, col3 ) VALUES (?,?,?)"),
330        ("INSERT INTO VALUE (col1,col2,col3) VALUES ('a', \'b\' ,1)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?, ? ,?)"),
331        ("INSERT INTO VALUE (col1, col2, col3) VALUES ('a', \'b\', 1)" , "INSERT INTO VALUE (col1, col2, col3) VALUES (?, ?, ?)"),
332        ("INSERT INTO VALUE ( col1, col2, col3 ) VALUES ('a', \'b\', 1)" , "INSERT INTO VALUE ( col1, col2, col3 ) VALUES (?, ?, ?)"),
333        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'罿潯罿潯罿潯罿潯罿潯\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
334        ("INSERT INTO VALUE (col1,col2,col3) VALUES (X'ff',\'罿\',12983)" , "INSERT INTO VALUE (col1,col2,col3) VALUES (?,?,?)"),
335        ("SELECT 3 AS NUCLEUS_TYPE,A0.ID,A0.\"NAME\" FROM \"VALUE\" A0" , "SELECT ? AS NUCLEUS_TYPE,A0.ID,A0.\"NAME\" FROM \"VALUE\" A0"),
336        ("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 > ?"),
337        ("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 > ?"),
338        ("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 > ?"),
339        ("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 > ?"),
340        ("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 > ?"),
341        ("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 > ?"),
342        ("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 > ?"),
343        ("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 > ?"),
344        ("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 > ?"),
345        ("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 > ?"),
346        ("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 > ?"),
347        ("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 > ?"),
348        ("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 > ?"),
349        ("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 <> ?"),
350        ("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 <> ?"),
351        ("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 <> ?"),
352        ("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 <> ?"),
353        ("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 <> ?"),
354        ("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 <> ?"),
355        ("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 <> ?"),
356        ("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 <> ?"),
357        ("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 <> ?"),
358        ("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 <> ?"),
359        ("CREATE TABLE S_H2 (id INTEGER not NULL, PRIMARY KEY ( id ))" , "CREATE TABLE S_H2 (id INTEGER not NULL, PRIMARY KEY ( id ))"),
360        ("CREATE TABLE S_H2 ( id INTEGER not NULL, PRIMARY KEY ( id ) )" , "CREATE TABLE S_H2 ( id INTEGER not NULL, PRIMARY KEY ( id ) )"),
361        ("SELECT * FROM TABLE WHERE name = 'O''Brady'" , "SELECT * FROM TABLE WHERE name = ?"),
362        ("INSERT INTO visits VALUES (2, 8, '2013-01-02', 'rabies shot')" , "INSERT INTO visits VALUES (?, ?, ?, ?)"),
363        (
364            "SELECT
365\tcountry.country_name_eng,
366\tSUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls,
367\tAVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_difference
368FROM country
369LEFT JOIN city ON city.country_id = country.id
370LEFT JOIN customer ON city.id = customer.city_id
371LEFT JOIN call ON call.customer_id = customer.id
372GROUP BY
373\tcountry.id,
374\tcountry.country_name_eng
375HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
376ORDER BY calls DESC, country.id ASC;",
377                "SELECT
378\tcountry.country_name_eng,
379\tSUM(CASE WHEN call.id IS NOT NULL THEN ? ELSE ? END) AS calls,
380\tAVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),?)) AS avg_difference
381FROM country
382LEFT JOIN city ON city.country_id = country.id
383LEFT JOIN customer ON city.id = customer.city_id
384LEFT JOIN call ON call.customer_id = customer.id
385GROUP BY
386\tcountry.id,
387\tcountry.country_name_eng
388HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),?)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
389ORDER BY calls DESC, country.id ASC;"
390        ),
391        ("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;"),
392        (
393            "UPDATE v_country_all /* 1. in-line comment */ SET
394/*
395    * 2. multi-line comment
396    */
397country_name = 'Nova1'
398-- 3. single-line comment
399WHERE id = 8;",
400                "UPDATE v_country_all /* ? in-line comment */ SET
401/*
402    * ? multi-line comment
403    */
404country_name = ?
405-- ? single-line comment
406WHERE id = ?"
407        ),
408        (
409            "INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Deutschland', 'Germany', 'DEU');
410INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Srbija', 'Serbia', 'SRB');
411INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Hrvatska', 'Croatia', 'HRV');
412INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('United States of America', 'United States of America', 'USA');
413INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Polska', 'Poland', 'POL');",
414                "INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
415INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
416INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
417INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);
418INSERT INTO country (country_name, country_name_eng, country_code) VALUES (?, ?, ?);"
419        ),
420        ("SELECT * FROM TABLE WHERE userId = ',' and foo=foo.bar", "SELECT * FROM TABLE WHERE userId = ? and foo=foo.bar"),
421        ("SELECT * FROM TABLE WHERE userId =     ','||foo.bar", "SELECT * FROM TABLE WHERE userId =     ?||foo.bar"),
422        (
423            concat!(
424            "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)) ) ",
425            "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)) )"),
426            concat!(
427                "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)) ) ",
428                "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)) )"
429            )
430        ),
431    ];
432}
libdd_trace_obfuscation/sql.rs

libdd_trace_obfuscation/
sql.rs
