Transcript SQL Injection - Professional Association for SQL Server

SQL Server and
Application Security for
Developers
Mladen Prajdić
SQL Server MVP
[email protected]
@MladenPrajdic
Sponsors
Feedback
http://speakerscore.com/sqlsaturday376
About me
Welcome to Slovenia
The sunny side of alps!
Security
Usability
Price
Pick two
Company Attack Vectors
• Website
•
•
•
•
SQL Injection
XSS, CSRF
DDOS
Other
• Social Engineering
• People impersonation
• Direct person interaction
• Others that I haven’t thought of
• GCHQ, NSA, CIA, etc 
SQL Injection
http://xkcd.com/327
SQL Injection
83% of hacks
Stats by FireHost.com
SQL Injection
SQL Injection
• Website attack with malicious SQL
• Error based
• Union based
• Blind
• Data destruction
• Data stealing
• Spam Redirects
SQL Injection - Prevention Tries
• Stored procedures
• Because they have parameters, right?
CREATE PROC spIAmVerySafe
@TableName varchar(256)
AS
EXEC('SELECT * FROM ' + @TableName);
GO;
CREATE PROC spNowIAmSafe
@ID int
AS
SELECT ID, FirstName, LastName
FROM Person
WHERE ID = @ID
GO;
SQL Injection - Prevention Tries
• Input validation
• Usually server and client keywords blacklists
• Replace all single quotes to 2 single quotes ‘ ->’’
• They are all USELESS!
DECLARE @s VARCHAR(MAX) = CONVERT(VARCHAR(MAX),
0x53454C454354202A2046524F4D207379732E7461626C6573);
EXEC(@s);
SELECT * FROM sys.tables
SQL Injection - The Only Protection
SQL Parameters
Use them properly!
SqlCommand cmd = new SqlCommand(sqlText, sqlConnection);
cmd.Parameters.Add("@IntParam", System.Data.SqlDbType.Int);
cmd.Parameters["@IntParam"].Value = 6;
SqlDataReader reader = cmd.ExecuteReader();
Cross-Site Scripting (XSS)
• Exploits the trust a user has for a particular site
• Perfect attack vector to use with SQL Injection
• Since 2007 about 84% of all client attacks
• About 70% of all websites are likely open to it
• Inject javascript into Web pages viewed by other
users
• Various JS client libraries bugs
• HTML, JS, Attribute encode/decode everything
Cross-Site Request Forgery (CSRF)
• Exploits the trust that a site has in a user's browser
• Attacks extremely under-reported
• Involve sites that rely on a user's identity
• Bank
• Exploit the site's trust in that identity
• Stored Cookie of the person you’re attacking
• Trick browser to send HTTP request to a target site
• Cookie authenticates and goes to the bank
• Involve HTTP requests that have side effects
• Withdraw money
DEMO
Distributed Denial Of Service (DDOS)
• Exploits the resources of your computer
• On average at least 1 person in your extended
family is unknowingly working for the Russian
mafia
• Extortion, Political agenda
• Feedly, Evernote
• Code Spaces
• Out of business
Amateurs hack
systems,
professionals hack
people
Social Engineering
• Exploits a person’s kindness and willingness to help
• Investment in security awareness in non-IT
employees: Minimal
• It is much easier to trick someone into giving a
password for a system than to spend the effort to
crack into the system (Kevin Mitnick)
Social Engineering - Profiling
Social Engineering – Contact
• Calling employees
• Call centers, pretending to be support or customer, …
• Getting various system information
• OS, Broswer, VPN client, WiFi, Anti-virus,…
• Phishing with XSS and CSRF included
• Giving away information not perceived to be
important
• Smart small talk
• Advanced target level
• Hot women in bars
• “Forgotten” or free USB sticks
Social Engineering - Prevention
• Stanley Mark Rifkin defrauded the Security Pacific
National bank in Los Angeles managed to steal
$10,200,000 in a single social engineering attack
• In 1978!
Educate people
Use two-factor authentication
Social Engineering
Success rate?
100%
Social Engineering
Clean up cost
for company between
$25,000 and $100,000
per incident
Securing SQL Server for Developers
So how can we
as developers protect our
Applications and SQL Servers?
Security Mechanisms Overview
• Run the SQL Server under a special domain account
• Create a new “SqlRunner” user in AD
• Give it minimal permission to the domain and computer
• Use it to run SQL Server
• DBA realm
• Transparent DB encryption
• SQL Server Audit
• Reducing the possible surface attack vector
Security Mechanisms Overview
• Securables
• Objects that can be secured with permissions
• Principals
• People/Processes that access securables
• GRANT, DENY, REVOKE
• DENY always has priority
• Various Cryptographic functions
• EncryptBy*, DecryptBy*, SignBy*, HASHBYTES, …
Permissions Hierarchy - Principals
Windows
Server
Database
Windows
Group
SQL Server
Login
Database
User
Windows
Domain
Login
Fixed Server
Role
Fixed
Database
Role
Windows
Local Login
User-defined
Fixed Server
Role
User-defined
Database
Role
Permissions Hierarchy - Securables
Server
Database
SQL Server
Login
Schema
Endpoint
User, Certificate, Role, …
Database
Table, View, Function, Stored
Procedure, Type, …
Permissions Hierarchy - Example
Windows
Domain
Login
OR
SQL Server
Login
Database
User
Maps 1:1
Depending on
permissions from
Treat the database
access objects as an
interface
User
Permissions
User Roles
Certificates
Return data from
Object
Access
Schema
DEMO
SET TRUSTWORTHY ON “hole”
• If DB is trustworthy
• If DB owner login is a sysadmin
• If YourAppLogin’s user is member of db_owner role
• YourAppLogin can elevate himself to sysadmin
• Let’s secure it properly:
• YourAppLogin with no default permissions
• DB owner’s login in public role only
• No users in database in db_owner role
DEMO
.Net Connection Encryption
• SSL
• Install CA cert on the server or server self-signed cert
• If required by Server (encrypt all connections)
• ForceEncryption=Yes in SQL Server Configuration Manager
• No client change required
• If required by Client (encrypt just one connection)
• On the server encryption is an option
• Encrypt=true in connection string (TrustServerCertificate)
• sys.dm_exec_connections.encrypt_option
• IPsec
• both client and server OS must support it
Things to Remember - SQL
• Use login/user with least privileges
• Run SQL Server service with a custom account
• Use SQL parameters
• No SysAdmin (SA) or SET TRUSTWORTHY ON
• No sysadmin database owners
• Treat the database access objects as secure interface
Things to Remember - .Net
• Machine.config
<system.web>
<deployment retail="true" />
• Web.config
</system.web>
• Redirect to custom error pages
<customErrors mode="On" defaultRedirect="defaultURL" >
<error statusCode="404" redirect="url" />
</customErrors>
• HTML encode/decode all traffic from/to DB
• Microsoft Web Protection Library (AntiXSS)
• Nuget
• Also part of the Microsoft SDL tools
Things to Remember - Social
• Watch out for hot blondes in the bar
• Split your security budget
• 80%: sysadmin education
• 20%: people education
• Metasploit
• Social-Engineer Toolkit (SET)
The less data
you store
the safer you are