Justin Clarke Lead Author and Technical Editor Rodrigo Marcos Alvarez Gary O’Leary-Steele Dave Hartley Alberto Revelli Joseph Hemler Marco Slaviero Alexander Kornbrust Dafydd Stuttard Haroon Meer Elsevier, Inc., the author(s), and any person or firm involved in the writing, editing, or production (collectively “Makers”) of this book (“the Work”) do not guarantee or warrant the results to be obtained from the Work. There is no guarantee of any kind, expressed or implied, regarding the Work or its contents. The Work is sold AS IS and WITHOUT WARRANTY. You may have other legal rights, which vary from state to state. In no event will Makers be liable to you for damages, including any loss of profits, lost savings, or other incidental or consequential damages arising out from the Work or its contents. Because some states do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you. You should always use reasonable care, including backup and other appropriate precautions, when working with computers, networks, data, and files. Syngress Media®, Syngress®, “Career Advancement Through Skill Enhancement®,” “Ask the Author UPDATE®,” and “Hack Proofing®,” are registered trademarks of Elsevier, Inc. “Syngress: The Definition of a Serious Security Library”™, “Mission Critical™,” and “The Only Way to Stop a Hacker is to Think Like One™” are trademarks of Elsevier, Inc. Brands and product names mentioned in this book are trademarks or service marks of their respective companies. PUBLISHED BY Syngress Publishing, Inc. Elsevier, Inc. 30 Corporate Drive Burlington, MA 01803 SQL Injection Attacks and Defense Copyright © 2009 by Elsevier, Inc. All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication. Printed in the United States of America 1 2 3 4 5 6 7 8 9 0 ISBN 13: 978-1-59749-424-3 Publisher: Laura Colantoni Page Layout and Art: SPI Acquisitions Editor: Rachel Roumeliotis Copy Editor: Audrey Doyle Developmental Editor: Matthew Cater Indexer: SPI Lead Author and Technical Editor: Justin Clarke Cover Designer: Michael Kavish Project Manager: Heather Tighe For information on rights, translations, and bulk sales, contact Matt Pedersen, Director of Corporate Sales, Elsevier; email [email protected]. Library of Congress Cataloging-in-Publication Data Application Submitted Lead Author and Technical Editor Justin Clarke is a co-founder and Director of Gotham Digital Science, an information security consulting firm that works with clients to identify, prevent, and manage security risks. He has over twelve years’ experience in testing the security of networks, web applications, and wireless networks for large financial, retail, and technology clients in the United States, United Kingdom and New Zealand. Justin is a contributing author to a number of computer security books, as well as a speaker at many conferences and events on security topics, including Black Hat USA, EuSecWest, OSCON, ISACA, RSA, SANS, OWASP, and the British Computer Society. He is the author of the Open Source SQLBrute blind SQL injection exploitation tool, and is the Chapter Leader for the London chapter of OWASP. iii Contributing Authors Rodrigo Marcos Alvarez (MSc, BSc, CREST, CISSP, CNNA, OPST, MCP) is the founder and technical director of SECFORCE. SECFORCE is a UK-based IT security consultancy that offers vendor-independent and impartial IT security advice to companies across all industry fields. Rodrigo is a contributor to the OWASP project and a security researcher. He is particularly interested in network protocol analysis via fuzzing testing. Among other projects, he has released TAOF, a protocol agnostic GUI fuzzer, and proxyfuzz, a TCP/UDP proxy which fuzzes on the fly. Rodrigo has also contributed to the web security field by releasing bsishell, a python interacting blind SQL injection shell and developing TCP socket reusing attacking techniques. Dave Hartley has been working in the IT security industry since 1998. He is currently a security consultant for Activity Information Management, based in the United Kingdom, where he is responsible for the development and delivery of Activity’s technical auditing services. Dave has performed a wide range of security assessments and provided a myriad of consultancy services for clients in a number of different sectors, including financial institutions, entertainment, media, telecommunications, and software development companies and government organizations worldwide. Dave is a CREST certified consultant and part of Activity’s CESG CHECK team. He is also the author of the Bobcat SQL injection exploitation tool. Dave would like to express heartfelt thanks to his extremely beautiful and understanding wife Nicole for her patience and support. Joseph Hemler (CISSP) is a co-founder and Director of Gotham Digital Science, an information security consulting firm that works with clients to identify, prevent, and manage security risks. He has worked in the realm of application security for over 9 years, and has deep experience identifying, iv exploiting, and correcting software security flaws. Prior to founding GDS, Mr. Hemler was a senior security engineer at Ernst & Young’s Advanced Security Center. Mr. Hemler has authored source code analysis tools and written multiple scripts for identifying and exploiting network and web application vulnerabilities. He is a contributing author to books in the area of application security, frequently blogs on the GDS Security Blog, and often speaks at various information security conferences and training seminars. Mr. Hemler graduated with a Bachelors of Business Administration from the University of Notre Dame. Alexander Kornbrust is the founder of Red-Database-Security. He provides Oracle security audits, security training and consulting to customers worldwide. Alexander has worked since 1992 with Oracle and his specialties are the security of Oracle databases and secure architectures. Alexander has reported more than 300 security bugs to Oracle. Alexander holds a masters degree (Diplom-Informatiker) in computer science from the University of Passau. Haroon Meer is the Technical Director of SensePost. He joined SensePost in 2001 and has not slept since his early childhood. He has played in most aspects of IT Security from development to deployment and currently gets most of his kicks from reverse engineering, application assessments, and similar forms of pain. Haroon has spoken and trained at Black Hat, Defcon, Microsoft Tech-Ed, and other conferences. He loves “Deels,” building new things, breaking new things, reading, deep find-outering, and making up new words. He dislikes sleep, pointless red-tape, dishonest people, and watching cricket. Gary O’Leary-Steele (CREST Consultant) is the Technical Director of Sec-1 Ltd, based in the UK. He currently provides senior-level penetration testing and security consultancy for a variety of clients, including a number of large online retailers and financial sector organizations. His specialties v include web application security assessment, network penetration testing and vulnerability research. Gary is also the lead author and trainer for the Sec-1 Certified Network Security Professional (CNSP) training program that has seen more than 3,000 attendees since its launch. Gary is credited by Microsoft, RSA, GFI and Marshal Software for the discovery of security flaws within their commercial applications. Alberto Revelli is a security researcher and the author of sqlninja, an open source toolkit that has become a “weapon of choice” when exploiting a SQL Injection vulnerability on a web application based on Microsoft SQL Server. As for his day job, he works as a senior security consultant for Portcullis Computer Security, mostly breaking into web applications and into any other thing that happens to tickle his curiosity. During his career he has assisted a multitude of clients including major financial institutions, telecom operators, media and manufacturing companies. He has been invited as a speaker to several security conferences, including EuSecWest, CONFidence, Shakacon, and SOURCE. He is the Technical Director of the Italian Chapter of OWASP and he is one of the authors of the OWASP Testing Guide. Prior to joining Portcullis, Alberto worked for Spike Reply and McKinsey&Company. He currently resides in London, enjoying its awful weather and its crazy nightlife together with his girlfriend. Marco Slaviero (MSc) is an associate at SensePost, a South African information security company focused on providing penetration testing services to global clients in the financial services, mining and telecommunications sectors. Marco specializes in web application assessments with a side interest in thick applications and network assessments. Marco has spoken on SQL Injection at Black Hat USA, and he developed the proof-of-concept Squeeza tool. Marco lives with Juliette, his wonderful wife, who gave him the space to contribute to this book. vi Dafydd Stuttard is the author of the best-selling Web Application Hacker’s Handbook. Under the alias “PortSwigger” he created the popular Burp Suite of web application hacking tools. Dafydd has developed and presented training courses at the Black Hat security conferences around the world. Dafydd is a Principal Security Consultant at Next Generation Security Software, where he leads the web application security competency. He has ten years’ experience in security consulting and specializes in the penetration testing of web applications and compiled software. Dafydd holds Masters and Doctorate degrees in philosophy from the University of Oxford. vii This page intentionally left blank Contents Chapter 1 What Is SQL Injection? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Understanding How Web Applications Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 A Simple Application Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 A More Complex Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Understanding SQL Injection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 High-Profile Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Understanding How It Happens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Dynamic String Building . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Incorrectly Handled Escape Characters . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Incorrectly Handled Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Incorrectly Handled Query Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Incorrectly Handled Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Incorrectly Handled Multiple Submissions . . . . . . . . . . . . . . . . . . . . . . . 19 Insecure Database Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Solutions Fast Track . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Frequently Asked Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Chapter 2 Testing for SQL Injection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Finding SQL Injection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Testing by Inference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Identifying Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 GET Requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 POST Requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Other Injectable Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Manipulating Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Information Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Database Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Commonly Displayed SQL Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Microsoft SQL Server Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 MySQL Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Oracle Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 ix