4.2 Breaking Down a Shiny App

I built a simple shiny app to illustrate how injection works. The app takes an email address and job title as inputs and saves them to a SQL Server database. Users can also update and delete existing records. The app persists user input in one table named dbo.Persons. The following SQL creates dbo.Persons and then inserts one row. The last statement selects all the rows.

SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Persons;
CREATE TABLE dbo.Persons (
  email Varchar(100) PRIMARY KEY, 
  jobtitle Varchar(100)
);

INSERT INTO dbo.Persons VALUES ('q1724449@nwytg.net','Analyst');

SELECT email, jobtitle FROM dbo.Persons;

The shiny UI controls provide input to the following queries. R combines the user input and query code.

-- Select all rows from the dbo.Persons table
SELECT * 
FROM dbo.Persons

--Get the jobtitle where the email address matches the user-provided email
SELECT jobtitle 
FROM dbo.Persons 
WHERE email = 'User Input'

--Update the Email and job title where the email address matches the user-provided email
UPDATE dbo.Persons 
SET Email = 'User Input',jobtitle=  'User Input' 
WHERE email ='User Input')

--Delete a row where the email matches user input
DELETE 
FROM dbo.Persons 
WHERE Email = 'User Input'

--add a new row to the dbo.Persons table based on user-provided email and jobtitle
INSERT INTO dbo.Persons(email,jobtitle) 
VALUES ('User Input','User Input')

SELECT, UPDATE, DELETE, and INSERT are the fundamental operations of data modification language (DML). My sample app only executes DML code. The other types of query code are data control language (DCL) and data definition language (DDL). I have not seen many apps use DCL and DDL, but there’re times when it’s useful. The CREATE TABLE query above is an example of DDL. The code below concatenates the user input with query code and executes the statement.

    dbGetQuery(myPool,
               paste0("Select jobtitle 
                      From dbo.Persons 
                      Where email   ='",input$titled,"'"))

    dbGetQuery(myPool,
               paste0("UPDATE dbo.Persons 
                       SET Email = '",input$emailupdate,"',jobtitle=  '",input$titleupdate,"'
                       Where email ='",input$titled,"'"))
    
    dbGetQuery(myPool,
               paste0("DELETE 
                       From dbo.Persons 
                       WHERE Email = '",input$titled,"'"))
    
    dbGetQuery(myPool
               ,paste0("Insert into dbo.Persons 
                        values ('",input$email,"','",input$title,"')"))

The code is vulnerable to SQL injection. Nothing is stopping a user from passing a SQL script to either one of the inputs. The GIF below first shows how a user a suppose to interact with the app. Then it shows how a hacker can pass a malicious script through the email input field.

The string executed against the database is two separate SQL commands. The second command truncates the Persons table. In a real-world production system, this might be data loss or application downtime.

email <- "'','') TRUNCATE TABLE dbo.Persons --"
title <- "Analyst"
paste0("Insert into dbo.Persons values ('",email,"','",title,"')")