Housekeeping

1
Housekeeping

• Project/assignment 6/quiz 6 questions?
• Quiz 6 Query optimization, database security
• At 910, youll have 15 minutes to do on-line
  student ratings
• Office hours today 1030-1230
• Offce hours next week
• M/W/F 1030-1230

2
Security Access Control, SQL Injection Attacks

• Based upon slides from classes.soe.ucsc.edu/.../S
  QL20Injection20Attacks.ppt
• homes.cs.washington.edu/suciu/current-trends.ppt
• www.cse.iitb.ac.in/dbms/Data/.../DBSecurity-Overvi
  ew.ppt

3
Data Security

• Protection from malicious attempts to steal
  (view) or modify data.
• The science and study of methods of protecting
  data (...) from unauthorized disclosure and
  modification
• Data Security Confidentiality Integrity

4
Traditional Data Security

• Security in statistical databases Theory
• http//en.wikipedia.org/wiki/Statistical_database
• In a statistical database, it is often desired to
  allow query access only to aggregate data, not
  individual records. Securing such a database is a
  difficult problem, since intelligent users can
  use a combination of aggregate queries to derive
  information about a single individual.
• Security in SQL Access control Views

5
Access Control in SQL
GriffithWade'76, Fagin'78
GRANT privileges ON object TO users
WITH GRANT OPTIONS
privileges SELECT INSERT DELETE . .
. object table attribute
REVOKE privileges ON object FROM users
CASCADE
6
Access Control in MySQL

• http//dev.mysql.com/doc/refman/5.0/en/privilege-s
  ystem.html
• The primary function of the MySQL privilege
  system is to authenticate a user who connects
  from a given host and to associate that user with
  privileges on a database such as SELECT, INSERT,
  UPDATE, and DELETE
• There are some things that you cannot do with the
  MySQL privilege system
• You cannot explicitly specify that a given user
  should be denied access. That is, you cannot
  explicitly match a user and then refuse the
  connection.
• You cannot specify that a user has privileges to
  create or drop tables in a database but not to
  create or drop the database itself.
• A password applies globally to an account. You
  cannot associate a password with a specific
  object such as a database, table, or routine.

7
Views in SQL

• A SQL View (almost) any SQL query
• Typically used as

CREATE VIEW pmpStudents AS SELECT FROM
Students WHERE
GRANT SELECT ON pmpStudents TO DavidRispoli
8
Views in MySQL

• http//dev.mysql.com/doc/refman/5.0/en/create-view
  .html
• CREATE OR REPLACE
• ALGORITHM UNDEFINED MERGE TEMPTABLE
• DEFINER user CURRENT_USER
• SQL SECURITY DEFINER INVOKER
• VIEW view_name (column_list)
• AS select_statement
• WITH CASCADED LOCAL CHECK OPTION
• The DEFINER and SQL SECURITY clauses determine
  which MySQL account to use when checking access
  privileges for the view when a statement is
  executed that references the view.

9
Summary of SQL Security

• Limitations
• Often no row level access control
• Note DB specific fine-grained access control
  is an active area of improvement
• Table creator owns the data (not always fair)

Access control great success story of the DB
community...

• or spectacular failure
• Only 30 assign privileges to users/roles
• And then to protect entire tables, not columns

10
MySQL security

• http//dev.mysql.com/doc/refman/5.0/en/security.ht
  ml
• Many aspects
• General factors that affect security. These
  include choosing good passwords, not granting
  unnecessary privileges to users, ensuring
  application security by preventing SQL injections
  and data corruption, and others. See Section 6.1,
  General Security Issues.
• Security of the installation itself. The data
  files, log files, and the all the application
  files of your installation should be protected to
  ensure that they are not readable or writable by
  unauthorized parties. For more information, see
  Section 2.18, Postinstallation Setup and
  Testing.

11
MySQL security

• Access control and security within the database
  system itself, including the users and databases
  granted with access to the databases, views and
  stored programs in use within the database. For
  more information, see Section 6.2, The MySQL
  Access Privilege System, and Section 6.3, MySQL
  User Account Management.
• Network security of MySQL and your system. The
  security is related to the grants for individual
  users, but you may also wish to restrict MySQL so
  that it is available only locally on the MySQL
  server host, or to a limited set of other hosts.
• Ensure that you have adequate and appropriate
  backups of your database files, configuration and
  log files. Also be sure that you have a recovery
  solution in place and test that you are able to
  successfully recover the information from your
  backups. See Chapter 7, Backup and Recovery.

12
SQL Injection Attacks
13
http//www.circleid.com/posts/20130325_sql_injecti
on_in_the_wild/
14
What is a SQL Injection Attack?

• Many web applications take user input from a form
• Often this user input is used literally in the
  construction of a SQL query submitted to a
  database. For example
• SELECT productdata FROM table WHERE productname
  user input product name
• A SQL injection attack involves placing SQL
  statements in the user input

15
SQL Injection Attacks on the rise

• https//www.net-security.org/secworld.php?id13313
  
• Many, many sites have lost customer data in this
  way, said Chris Hinkley, Senior Security
  Engineer at FireHost. SQL Injection attacks are
  often automated and many website owners may be
  blissfully unaware that their data could actively
  be at risk. These attacks can be detected and
  businesses should be taking basic and blanket
  steps to block attempted SQL Injection, as well
  as the other types of attacks we frequently see.

16
2012 News of SQL attacks

• http//www.mysqlperformanceblog.com/2012/07/18/sql
  -injection-still-a-problem/
• An SQL injection vulnerability resulted in an
  urgent June bugfix release of Ruby on Rails 3.x.
• Yahoo! Voices was hacked in July. The attack
  acquired 453,000 user email addresses and
  passwords. The perpetrators claimed to have used
  union-based SQL injection to break in.
• LinkedIn.com leaked 6.5 million user credentials
  in June. A class action lawsuit alleges that the
  attack was accomplished with SQL injection.
• SQL injection was documented as a security threat
  in 1998, but new incidents still occur every
  month. Making honest mistakes, developers fail
  to defend against this means of attack, and the
  security of online data is at risk for all of us
  because of it.

17
Some good sites to learn more

• Prevention guide (with sample code in many
  languages)
• http//bobby-tables.com/
• Tutorials
• (webinar) http//www.percona.com/webinars/2012-07-
  25-sql-injection-myths-and-fallacies
• http//www.netrostar.com/SQL-Injection-Attack
• http//www.unixwiz.net/techtips/sql-injection.html
  
• Cool site that lets you try out attacks on a
  sample DB and explains why they work
• http//sqlzoo.net/hack/
• Research paper on how to retrofit existing
  websites to combat SQL injection attacks
• http//lersse-dl.ece.ubc.ca/record/205/files/paper
  .pdf

18
An Example SQL Injection Attack

• Product Search
• This input is put directly into the SQL statement
  within the Web application
• query SELECT prodinfo FROM prodtable WHERE
  prodname . _POSTprod_search .
• Creates the following SQL
• SELECT prodinfo FROM prodtable WHERE prodname
  blah OR x x
• Attacker has now successfully caused the entire
  database to be returned.

blah OR x x
19
A More Malicious Example

• What if the attacker had instead entered
• blah DROP TABLE prodinfo --
• Results in the following SQL
• SELECT prodinfo FROM prodtable WHERE prodname
  blah DROP TABLE prodinfo --
• Note how comment (--) consumes the final quote
• Causes the entire database to be deleted
• Depends on knowledge of table name
• This is sometimes exposed to the user in debug
  code called during a database error
• Use non-obvious table names, and never expose
  them to user
• Usually data destruction is not your worst fear,
  as there is low economic motivation

20
Other injection possibilities

• Using SQL injections, attackers can
• Add new data to the database
• Could be embarrassing to find yourself selling
  politically incorrect items on an eCommerce site
• Perform an INSERT in the injected SQL
• Modify data currently in the database
• Could be very costly to have an expensive item
  suddenly be deeply discounted
• Perform an UPDATE in the injected SQL
• Often can gain access to other users system
  capabilities by obtaining their password

21
Best defence

• If possible, use bound variables with prepared
  statements
• Many libraries allow you to bind inputs to
  variables inside a SQL statement
• PERL example (from http//www.unixwiz.net/techtips
  /sql-injection.html)
• sth dbh-gtprepare("SELECT email, userid FROM
  members WHERE email ?")
• sth-gtexecute(email)
• See http//bobby-tables.com for example code in
  many languages

22
How does this prevent an attack?

• The SQL statement you pass to prepare is parsed
  and compiled by the database server.
• By specifying parameters (either a ? or a named
  parameter like name) you tell the database
  engine what to filter on.
• Then when you call execute the prepared statement
  is combined with the parameter values you
  specify.
• It works because the parameter values are
  combined with the compiled statement, not a SQL
  string.
• SQL injection works by tricking the script into
  including malicious strings when it creates SQL
  to send to the database. So by sending the actual
  SQL separately from the parameters you limit the
  risk of ending up with something you didn't
  intend.

23
Other Defenses

• Use provided functions for escaping strings
• Many attacks can be thwarted by simply using the
  SQL string escaping mechanism
• ? \ and ? \
• mysql_real_escape_string() is the preferred
  function for this
• Will not guard against all attacks
• Consider
• SELECT fields FROM table WHERE id 23 OR 11
• No quotes here!

24
More Defenses

• Check syntax of input for validity
• Many classes of input have fixed languages
• Email addresses, dates, part numbers, etc.
• Verify that the input is a valid string in the
  language
• Some languages allow problematic characters
  (e.g., in email) may decide to not allow
  these
• Exclude quotes and semicolons
• Not always possible consider the name Bill
  OReilly
• Want to allow the use of single quotes in names
• Have length limits on input
• Many SQL injection attacks depend on entering
  long strings

25
Even More Defenses

• Scan query string for undesirable word
  combinations that indicate SQL statements
• INSERT, DROP, etc.
• If you see these, can check against SQL syntax to
  see if they represent a statement or valid user
  input
• Limit database permissions and segregate users
• If youre only reading the database, connect to
  database as a user that only has read permissions
• Never connect as a database administrator in your
  web application

26
And Yet More Defenses

• Configure database error reporting
• Default error reporting often gives away
  information that is valuable for attackers (table
  name, field name, etc.)
• Configure so that this information is never
  exposed to a user