4.3 Defending Against Injection

4.3.1 Paramertization and String Escaping

The app mixes trusted data with untrusted data. Trusted data is the query code, and untrusted data is user input. If untrusted data mixes with trusted data, then query code becomes changeable at run time. I can start to separate the trusted and untrusted data through parameterized queries. Instead of passing the user input variables to the query string, I store them in parameters. Then I reference the parameters instead of the user variables.

query <- sqlInterpolate(con,"INSERT INTO dbo.Persons 
                             VALUES (?email,?title)",input$email,input$title))

dbGetQuery(myPool, query)

The sqlInterpolate function helps isolate the user input from the query string. Also, sqlinterpolate escapes single tick marks making it difficult to execute ad-hoc scripts. The injection attack fails this time because the code handles input as one continuous string.

4.3.2 Whitelist input

The app should only allow valid characters as input. If a user enters an email with spaces, then the app should reject it. I implemented an email whitelist by comparing the input with a Regex pattern.

emailwhitelist <- "^[[:alnum:].-_]+@[[:alnum:].-]+$"

   if(!is.na(str_match(input$email, emailwhitelist))){
    query <- sqlInterpolate(con,
                            "INSERT INTO dbo.Persons 
                            VALUES(?email,?title)",input$email,input$title))
  dbGetQuery(myPool, query)
     } else {stop("Not a valid email.")}

Developers should whitelist with caution. Using a restrictive character set could block legitimate input. The Regex pattern above blocks email addressed with a single quote. Are quotes possible in emails? If they aren’t allowed will that change in the future? It’s possible. A whitelist requires a balance between security and usability.

4.3.3 Server Side Defense

The database user account should have minimal permissions. I would not have been able to truncate the table in the example above had I only had permission to SELECT and INSERT. Interfaces simplify permissions. Instead of granting permissions on tables I can grant permissions on the interface. Stored procedures make great interfaces. Developers can grant permissions on stored procedures without granting access to tables. Thus DB all interactions occur in the manner defined by the stored procedure.

I can wrap the INSERT statement into a stored procedure. The shinybuilder_app user cannot run an INSERT statement outside of the stored procedure.

Use Cab_Demo
GO

DROP PROCEDURE IF EXISTS dbo.uspInsertEmail;
GO
CREATE PROCEDURE dbo.uspInsertEmail
@email Varchar(100),
@title Varchar(100)
AS
INSERT INTO dbo.Persons
VALUES (@email,@title)

GO

GRANT EXECUTE ON dbo.uspInsertEmail TO shinybuilder_app 

Now the app calls the stored procedure instead of ad-hoc SQL.

  query <- sqlInterpolate(con
                          ,"EXECUTE dbo.uspInsertEmail ?email,?title",input$email,input$title))

  dbGetQuery(myPool, query)

4.3.4 Other defense layers

• A few lines of defense exist outside of the app and database server. An intrusion detection system (IDS) uses signatures to detect potential attacks. Some IDS also provide real-time alerting features.

• Vendor provided patches often contain security fixes. System admins or developers should apply security fixes as soon as possible.

4.3.5 Defense in Depth

Even big firms struggle to manage injection risks, and no code is 100% secure. However, parametrization and whitelists are good first lines of defense.