CM12004 Database Security (SQL Injection) PDF 2024

CM12004 Database Security (SQL Injection) James Davenport [email protected] University of Bath 9 December 2024 James [email protected] CM12004 Database Security (SQL Injection) 1 / 37...

CM12004 Database Security (SQL Injection) James Davenport [email protected] University of Bath 9 December 2024 James [email protected] CM12004 Database Security (SQL Injection) 1 / 37 Database Security Database A big file (or files) [Cynic’s Dictionary] True and therefore all the usual precautions apply. Ransomware Can encrypt a database without even knowing what it is.  Actually A big file with a lot of internal structure. Even if you pay the ransom, any error in the encryption/decryption process may destroy the structure of the database. !! [HYC23] reports that only 60% of those who paid the ransom got their data back immediately. James [email protected] CM12004 Database Security (SQL Injection) 2 / 37 What is SQL Injection? Injection “Injection flaws occur when untrusted data is sent [to] an interpreter as part of a command or query. The attacker’s hostile data can trick the interpreter into executing unintended commands or accessing data without proper authorization.” [Ope17, A1]. * Many flavours of injection: [Con21] describes an OGNL1 -Injection flaw in Atlassian’s Confluence source management system. SQL Structured Query Language — a language for interacting with databases. See [ISO16, eighth revision of 1986 standard].  !! Many flavours of SQL: the ‘S’ is not “Standard”. Therefore you may not wish to give away which version of database you have behind your website. Current! “70% of Ruby on Rails exploits were SQLi” [Sqr20] Who would be so dumb as to allow injection? Apparently many people, but why? 1 OGNL = Object-Graph Network Language. James [email protected] CM12004 Database Security (SQL Injection) 3 / 37 More about SQL SELECT * FROM users WHERE name = ’masjhd’; would be a typical SQL command to retrieve the record for masjhd from a table called users in a database. SQL statements are strings, obeyed at runtime by the database. Hence in a programming language one would write sql_query("SELECT * FROM users WHERE name = ’masjhd’;"); or something similar. But, of course, this only handles masjhd, not the name stored in a variable such as login. Hence, in a language where + is string concatenation, sql_query("SELECT * FROM users WHERE name = ’" +login+"’;"); will build the appropriate query for the value stored in the variable login. An API may well hide this SQL: return_user_data(login); James [email protected] CM12004 Database Security (SQL Injection) 4 / 37 SQL Injection [sar98] But (especially if there’s an API hiding the details) the programmer may forgot (or never realise) that this string is being parsed and interpreted at run-time by the SQL database: what  happens when the string login contains special characters? If the string is user input, we have no control over what the user inputs unless we enforce it. James [email protected] CM12004 Database Security (SQL Injection) 5 / 37 Bypassing checks ’SELECT * FROM usertable WHERE username="’+ owner + ’" AND accountno="’ + acct + ’";’ Example (Good) owner = Alice; acct = 01234567 looks up Alice with account 01234567 Example (Bad) owner = Alice; acct = 01234567" OR "1"="1 looks up Alice with any account number, as the condition always matches : note that the SQL interpreter sees SELECT * FROM usertable WHERE username="Alice" AND accountno="01234567" OR "1"="1";’ Example (Very Bad) owner = *; acct = 01234567" OR "1"="1 looks up any name with any account number James [email protected] CM12004 Database Security (SQL Injection) 6 / 37 Bolder attacks [XKC07, Eck18] Presumably something like INSERT INTO Students (name) VALUES (’Student Name’); and when Bobby Tables comes along INSERT INTO Students (name) VALUES (’Robert’); DROP TABLE Students; --’); noting that -- is the SQL comment, and DROP TABLE is the  command to delete a table. Sanitizing data inputs is not the best solution. James [email protected] CM12004 Database Security (SQL Injection) 7 / 37 Sanitization continued [Ver21] Ensure that client-side input validation isn’t the only line of defense. This validation is a great tool for improved user experience, but it doesn’t work as a security mechanism. It’s easy to remove client-side validation by altering JavaScript code loaded in the browser or do a basic HTTP call to the backend in a client-server architecture with a parameter that causes an SQL injection. Developers should be treating everything a client sends as potentially harmful and should therefore be validating on the server-side, ideally as close to the source as possible. To minimize the impact of an SQL injection, developers should be  strategic about an application’s privileges on a database. Why did the “Bobby Tables” data entry application have the privilege to drop a table? N.B. The cybersecurity principle of “least privilege” applies to applications as much as to humans. James [email protected] CM12004 Database Security (SQL Injection) 8 / 37 Second order attacks [Ora09] Suppose I want to find Jane’s account number, and the front-end looks it up via code like SELECT account FROM users WHERE username="’+username+’" into acct; Then if I can create my own username, I can create XXX" OR username="Jane, and if XXX doesn’t exist, I retrieve Jane’s account number. How to create: SQL isn’t a read-only language: see http: //www.unixwiz.net/techtips/sql-injection.html#addnew. James [email protected] CM12004 Database Security (SQL Injection) 9 / 37 Examples [Gre16] Apparently hostile state action against US Elections: note [FBI16, page 2] the use of DROP TABLE to remove evidence.  [Tra16] 35,000+ credit cards exposed. Note that this involved an attempt to bypass PCI DSS by collecting the data offline (to PCI) then “manually” processing it via an SAQ-B machine as if the customers were ’phoning in. [Fri17] A good walk-through of how a penetration tester might use SQL injection. 2022 https://wordpress.org/news/2022/01/ wordpress-5-8-3-security-release/ describes two SQL bugs (and others) in Wordpress! My most recent example is [SAN24] (23 May 2024) 10 vulnerabilities, all SQLi, in Ivant Endpoint Manager, in principle a security product. James [email protected] CM12004 Database Security (SQL Injection) 10 / 37 People who ought to know better can’t do sanitisation (1) [Tou22]: ineffective sanitisation in some of SonicWall’s security products. [Wiz22] shows that IBM can’t get it right even when protecting privileged operations. create_subscription is owned by, and runs as, the superuser. SELECT create_subscription(’test3’,’127.0.0.1’,’5432’,’a’, ’ibm’,’ibmclouddb’, ’test2_publication WITH(create_slot=false);’+ ’ INSERT INTO public.test3(data) VALUES(current_user);’) which does an insertion into public.test3(data). This example is deliberately harmless(ish), but shows injection. James [email protected] CM12004 Database Security (SQL Injection) 11 / 37 People who ought to know better can’t do sanitisation (2) Fortinet is a $5.3B cybersecurity company that develops and sells security solutions like firewalls, endpoint security and intrusion detection systems. [Con24]2 reports that a proof-of-concept attack is available for CVE-2023-48788, which “was reported to Fortinet as a zero-day by the UK National Cyber Security Centre (NCSC) and was actively exploited in the wild at the time of the patch, but likely in very targeted attacks”. The flaw is the result of improper sanitization of elements in an SQL command, which could be exploited in an SQL injection scenario to execute unauthorized code or com- mands on the FortiClient EMS [Endpoint Management System]. 2 Quoting https://fortiguard.fortinet.com/psirt/FG-IR-24-007. James [email protected] CM12004 Database Security (SQL Injection) 12 / 37 SQLi is alive and well in the 2020s I saw [Kan20]. This one is completely standard: “username: joke’ or ’1’=’1, password: joke’ or ’1’=’1; and comes with a complete Python exploit. [Ber21] shows how a firewall that attempts to sanitise can be bypassed by using floating-point notation. Hence, while the ’ OR ’1=1 bypass is blocked by the firewall, ’ OR 1.e(1) OR ’1=1 is not. This shows the risks of sanitisation. [Son22] shows that even OpenSSL can miss sanitisation cases. [Pro22, §4.4.2] reports that the FIN7 ransomware group have automated SQLi tools. [Gat23] reports that 29,000+ Internet-accessible Network Storage devices are vulnerable to SQL injection attacks. [PZYS23] noted that Text-to-SQL tools, e.g. those based on ChatGPT, could also generate SQL injection attacks. This lowers the threshold even further. But on the generation side, [Ull23] notes that Copilot generated prepared statements when asked to generate SQL. James [email protected] CM12004 Database Security (SQL Injection) 13 / 37 SQL is in unexpected places [Noz24] James [email protected] CM12004 Database Security (SQL Injection) 14 / 37 SQL is in unexpected places [Noz24] I had no idea that nutrunners (torque wrenches) would run Linux, or be vulnerable to SQL Injection (CVE-2023-48253)and XSS (CVE-2023-48248 etc.) we detail new vulnerabilities discovered in the Bosch Rexroth NXA015S-36V-B, a popular smart nutrunner (pneumatic torque wrench) used in automotive produc- tion lines. We demonstrate that these vulnerabilities could make it possible to implant ransomware on the device, which could be used to cause production line stoppages and potentially large-scale financial losses to asset owners. Another exploitation would allow the threat actor to hi- jack tightening programs while manipulating the onboard display, causing undetectable damage to the product being assembled or making it unsafe to use. James [email protected] CM12004 Database Security (SQL Injection) 15 / 37 But the big hack is MOVEit, hacked by Cl0p gang Figure: MOVEit and Cl0p’s ransomware note, from [Aka23] James [email protected] CM12004 Database Security (SQL Injection) 16 / 37 But the big hack is MOVEit, hacked by Cl0p gang (2) MOVEit is a “secure” File Transfer application, used, in particular, by several payroll companies. Progress Software (the authors) says thousands of enterprises, including 1,700 software companies and 3.5 million developers, use MOVEit. [Bru23] Figure: MOVEit timeline in 2023, from [Aka23] The breach of Zellis, a payroll services provider using the MOVEit tool, has been attributed to the compromise of multiple businesses in Britain and Ireland. Among the affected organisations are the BBC, British Airways, Boots and Aer Lingus. [Kun23] James [email protected] CM12004 Database Security (SQL Injection) 17 / 37 But the big hack is MOVEit, hacked by Cl0p gang (3) Figure: Vulnerable MOVEit server count from Shodan, from [Aka23] James [email protected] CM12004 Database Security (SQL Injection) 18 / 37 MOVEit victims (20/Dec/2023) https://konbriefing.com/en-topics/ cyber-attacks-moveit-victim-list.html 2611 organizations 85.1 - 89.9 m individuals Figure: Countries James [email protected] CM12004 Database Security (SQL Injection) 19 / 37 So what’s the answer to SQL Injection? 1 Read the advice at http://bobby-tables.com/! 2 If the language supports it, use parameterized queries: 3 If all else fails, then sanitize all your data before feeding it to  SQL. Note that sanitization is harder than it looks: see [Ros13, Fri17, Tre20]: The language of the web is full of special characters and strange markup (including alternate ways of representing the same characters), and efforts to authoritatively identify all “bad stuff” are unlikely to be successful. James [email protected] CM12004 Database Security (SQL Injection) 20 / 37 Parameterized Queries (Java) [Gna13] Example (Bad) String query = "SELECT * FROM users WHERE userid =’"+ userid + "’AND account=’" + account + "’"; Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query); Example (Good) PreparedStatement stmt = connection.prepareStatement ("SELECT * FROM users WHERE userid=? AND account=?"); stmt.setString(1, userid); stmt.setString(2, account); ResultSet rs = stmt.executeQuery(); The difference is that, in “bad” the parsing takes place after the user input is added, but in “good” it takes place before. James [email protected] CM12004 Database Security (SQL Injection) 21 / 37 So PreparedStatement is the answer? Not necessarily: Example (Bad) String q = "SELECT * FROM users WHERE userid =’"+ userid + "’ AND account=’" + account + "’"; PreparedStatement stmt = connection.prepareStatement(q); ResultSet rs = stmt.executeQuery(); This prepares a statement that contains user input, and is equivalent to the bad example on the previous slide. But a good prepared statement is! James [email protected] CM12004 Database Security (SQL Injection) 22 / 37 Education is necessary (which is why I am giving this lecture) The Gartner report [Gar22] on network firewalls doesn’t even mention SQLi filtering, whereas some firewalls do in fact offer it. Note that, even if you’re perfect, seeing SQLi is a good warning that you’re under attack. [TS19] points out that many database textbooks don’t cover SQLi at all, and several have dangerous examples given as “good  practice”. Before using a database textbook etc., check that the index contains “SQL Injection” and “Prepared Statement”. Most “No SQL” databases actually use SQL (or a variant): the phrase is a misnomer, and it’s really ”No ACID”: Atomicity, Consistency, Isolation and Durability. James [email protected] CM12004 Database Security (SQL Injection) 23 / 37 Risk and Mitigations The risk to our systems is high/critical: once injected, basically anything can be done Developers Train in SQLi and the use of prepared statements. Audit all SQL-using code. Open Source Audit all SQL-using code ourselves Closed Source Insist on an SQLi-focused penetration test report, or do a pentest ourselves Databases Be rigid on “least privilege” for all database accounts: use multiple accounts for different purposes. James [email protected] CM12004 Database Security (SQL Injection) 24 / 37 Bibliography I Akamai Security Intelligence Group (Ori David and Sam Tinklenberg and Maxim Zavodchik and and Ophir Harpaz). MOVEit SQLi Zero-Day (CVE-2023-34362) Exploited by CL0P Ransomware Group. https://www.akamai.com/blog/security-research/ moveit-sqli-zero-day-exploit-clop-ransomware, 2023. M.O. Bergeron. A Scientific Notation Bug in MySQL left AWS WAF Clients Vulnerable to SQL Injection. https://www.gosecure.net/blog/2021/10/19/ a-scientific-notation-bug-in-mysql-left-aws-waf-clients 2021. James [email protected] CM12004 Database Security (SQL Injection) 25 / 37 Bibliography II C. Brumfield. US feds stress urgent MOVEit platform patching after attacks hit agencies. https://www.csoonline.com/article/3700150/ us-feds-stress-urgent-moveit-platform-patching-after-at html, 2023. L. Constantin. Critical flaw in Atlassian Confluence actively exploited. https://www.csoonline.com/article/3632330/ critical-flaw-in-atlassian-confluence-actively-exploite html, 2021. James [email protected] CM12004 Database Security (SQL Injection) 26 / 37 Bibliography III L. Constantin. Exploit available for critical flaw in FortiClient Server. https://www.csoonline.com/article/2073837/ exploit-available-for-critical-flaw-in-forticlient-serv html, 2024. J. Eckert. How Little Bobby Tables Ruined the Internet or How To Protect User Data From SQL Injection. https://medium.com/@johnteckert/ how-little-bobby-tables-ruined-the-internet-d714c20d2ce 2018. James [email protected] CM12004 Database Security (SQL Injection) 27 / 37 Bibliography IV FBI. FBI Flash T-LD1004-TT (18 August 2016): Targeting Activity Against State Board of Election Systems. https://s.yimg.com/dh/ap/politics/images/boe_ flash_aug_2016_final.pdf, 2016. S. Friedl. SQL Injection Attacks by Example. http: //www.unixwiz.net/techtips/sql-injection.html, 2017. Gartner Inc. (Rajpreet Kaur and Adam Hils and Thomas Lintemuth). Magic Quadrant for Network Firewalls. Technical Report ID G00761497, 2022. James [email protected] CM12004 Database Security (SQL Injection) 28 / 37 Bibliography V S. Gatlan. Over 29,000 QNAP devices vulnerable to code injection attacks. https://www.bleepingcomputer.com/news/security/ over-29-000-qnap-devices-vulnerable-to-code-injection-a 2023. P. Gnanasundar. How to Fix SQL Injection Using Java PreparedStatement & CallableStatement. https: //software-security.sans.org/developer-how-to/ fix-sql-injection-in-java-using-prepared-callable-state 2013. James [email protected] CM12004 Database Security (SQL Injection) 29 / 37 Bibliography VI A. Greenberg. Hack Brief: As FBI Warns Election Sites Got Hacked, All Eyes Are on Russia. https://www.wired.com/2016/08/ hack-brief-fbi-warns-election-sites-got-hacked-eyes-rus 2016. HYCU. Ransomware Attacks — Never Pay the Ransom (Here’s Why). https://www.hycu.com/blog/ ransomware-attacks-dont-pay-the-ransom, 2023. ISO/IEC. ISO/IEC 9075:2016 Information technology — Database languages — SQL. https://www.iso.org/standard/63555.html, 2016. James [email protected] CM12004 Database Security (SQL Injection) 30 / 37 Bibliography VII M.Y. Kandemir. Hospital Management System 4.0 SQL Injection. https://packetstormsecurity.com/files/155803/ hms40-sql.txt, 2020. D. Kundaliya. Clop ransomware group gives BA, BBC and other victims one week to pay. https://www.computing.co.uk/news/4117466/ clop-ransomware-group-moveit-victims-deadline-pay, 2023. James [email protected] CM12004 Database Security (SQL Injection) 31 / 37 Bibliography VIII Nozomi Network Labs. Vulnerabilities on Bosch Rexroth Nutrunners May Be Abused to Stop Production Lines, Tamper with Safety-Critical Tightenings. https://www.nozominetworks.com/blog/ vulnerabilities-on-bosch-rexroth-nutrunners, 2024. Open Web Application Security Project (OWASP). The Ten Most Critical Web Application Security Risks (2017). https://owasp.org/www-project-top-ten/2017/, 2017. Oracle Corp. Examples of Second Order SQL Injection Attack. https://download.oracle.com/oll/tutorials/ SQLInjection/html/lesson1/les01_tm_attacks2.htm, 2009. James [email protected] CM12004 Database Security (SQL Injection) 32 / 37 Bibliography IX Prodaft. FIN7 Unveiled: A deep dive into notorious cybercrime gang. https: //www.prodaft.com/m/reports/FIN7_TLPCLEAR.pdf, 2022. X. Peng, Y. Zhang, J. Yang, and M. Stevenson. On the Security Vulnerabilities of Text-to-SQL Models. https://arxiv.org/abs/2211.15363v3, 2023. D. Ross. Insane in the IFRAME. https://web.archive.org/web/20150919051330/https: //www.owasp.org/images/3/3c/Insane_in_the_IFRAME_ -_David_Ross.pdf, 2013. James [email protected] CM12004 Database Security (SQL Injection) 33 / 37 Bibliography X SANS Institute. SANS NewsBites Vol. 26 Num. 41. https: //www.sans.org/newsletters/newsbites/xxvi-41, 2024. Jeff Forristal (signing as rain.forest.puppy). NT Web Technology Vulnerabilities. Phrack Magazine, 8(54):article 8, 1998. URL: https://web.archive.org/web/20140319065810/http:// www.phrack.com/issues.html?issue=54&id=8#article. D. Son. CVE-2022-2068: OpenSSL command injection vulnerability. https://securityonline.info/ cve-2022-2068-openssl-command-injection-vulnerability/, 2022. James [email protected] CM12004 Database Security (SQL Injection) 34 / 37 Bibliography XI Sqreen (Carlos Schults). Learnings from Sqreen’s State of App Sec Report: 70% of Ruby on Rails exploits were SQLi. https://blog.sqreen.com/app-sec-report-ruby/, 2020. B. Toulas. SonicWall: Patch critical SQL injection bug immediately. https://www.bleepingcomputer.com/news/security/ sonicwall-patch-critical-sql-injection-bug-immediately/ 2022. K. Trainer. Between 35,000 and 40,000 credit cards exposed to hackers after coding errors led to SQL Injection. https://www.foregenix.com/blog/ credit-cards-exposed-to-hackers-poor-coding-sql-injecti 2016. James [email protected] CM12004 Database Security (SQL Injection) 35 / 37 Bibliography XII Treadstone 71. #SQL Injection #GBK Character Encoding. https://cybershafarat.com/2020/12/25/sql_ injection-gbk-character-encoding/, 2020. C. Taylor and S. Sakharkar. ’);DROP TABLE textbooks;–: An Argument for SQL Injection Coverage in Database Textbooks. In Proceedings of the 50th ACM Technical Symposium on Computer Science Education, pages 191–197, 2019. J. Ullrich. Github Copilot vs. Google: Which code is more secure. https://isc.sans.edu/diary/Github+Copilot+vs+ Google+Which+code+is+more+secure/29918/, 2023. James [email protected] CM12004 Database Security (SQL Injection) 36 / 37 Bibliography XIII B. Vermeer. Top tips for preventing SQL injection attacks. https://www.helpnetsecurity.com/2021/09/08/ preventing-sql-injection-attacks/, 2021. Wiz (Ronen Shustin and Shir Tamari). Hell’s Keychain: Supply-chain vulnerability in IBM Cloud Databases for PostgreSQL allows potential unauthorized database access. https://www.wiz.io/blog/ hells-keychain-supply-chain-attack-in-ibm-cloud-databas 2022. XKCD. Cartoon 327. https://xkcd.com/327/, 2007. James [email protected] CM12004 Database Security (SQL Injection) 37 / 37