SQL Injection is a kind of attack done by adding SQL quires to the URL of the application. Such queries execute on the DBMS without having legitimate access to it. Such attacks are possible if there are some branches into the code. Let's see some code to understand it better:

dbGetQuery(conn, paste0(  "SELECT * FROM City LIMIT ", input$nrows, ";")) 

As we can see in the preceding code, input$nrows has been put directly into the query. If an attacker got access to this input$nrows, they could inject any SQL statement into it. In this case, the solution can be to prevent an attacker from passing vectors. So, the code can be modified as follows:

dbGetQuery(conn, paste0(  "SELECT * FROM City LIMIT ", as.integer(input$nrows)[1], ";")) 

The input is converted into an integer first. So, if an attacker puts some SQL into it, it will get converted into an integer and lose its meaning. This is an easy example. Let's discuss a more complex situation:

query<- paste0("SELECT * FROM City WHERE ID = '", input$ID, "';") 

In this code, if an attacker gets access to input$ID and manages to modify the value, they can get the data of a single city. For example, input$ID changed to 5 means that the data of the city with ID=5 will be invoked. But if they are trying to get information of all the cities, they can try OR 1 = 1 OR and can get data of all the city. Such input makes the condition always true. The solution to this attack is to use sqlInterpolate(). It can be used to interpolate the values into a SQL string, which prevents a SQL injection attack:

sql<- "SELECT * FROM City WHERE ID = ?id ;" 
query<- sqlInterpolate(conn, sql, id = input$ID) 

In the preceding code, if we change the input to ' OR 1 = 1 OR ', it will be converted into SELECT * FROM City WHERE ID = ''' OR 1 = 1 OR ''', where it has added extra (''). This will give blank table output and prevents SQL injection. Instead, if we use input$ID =6, it will change the query to SELECT * FROM City WHERE ID = '6', which is a valid ID. So the output will be the data of the city with ID=6.

This process of checking and converting user input into safe values is called sanitization. We must always take care to sanitize the user input to prevent SQL injection attacks.