Protecting against SQL Injection Attacks in Node.js.

Preventing SQL Injection Attacks

Another way to implement prepared statements is to use named placeholders. Instead of using an array, we use an object to map the parameters to the query variables.

Consider the following prepared statement using placeholders:
```js
db.all("SELECT * FROM Employee  WHERE FirstName = ? AND LastName = ? ", 
  [req.body.lastName, req.body.firstName], 
  (error, results) => {
  ...
});
```

The query can be reconstructed to use named placeholders by replacing the `?` with a variable beginning with the `$` character, and passing an object that maps to the named placeholder value.

```js
db.all("SELECT * FROM Employee  WHERE FirstName = $firstName AND LastName = $lastName ", 
  {
    $firstName: req.body.firstName,
    $lastName: req.body.lastName
  },
  (error, results) => {
  ...
});
```
This minimal change makes the code more readable for complicated queries.

Prepared Statements: Named Placeholders

One step of input sanitization is validating data input. Data validation is a process where a web-form checks if the information adheres to the expected format. `validator.js` provides many methods to check whether a string is a valid type. It can validate different types, ranging from dates to email addresses. 

Here are some of the ways we can validate input using `validator.js`: 
- `isEmail()` - Checks if the input is a valid email address.  
- `isLength()` - Checks if the input is a certain length. An object with `min` and `max` can be passed as an argument.
- `isNumeric()` - Checks if the input is numeric.
- `contains()` - Checks if the input contains a certain value.
- `isBoolean()` - Checks if the input is a boolean value.
- `isCurrency()` - Checks if the input is currency-formatted.
- `isJSON()` - Checks if the input is JSON.
- `isMobilePhone()` - Checks if the input is a valid mobile phone number.
- `isPostalCode()` - Checks if the input is a valid postal code.
- `isBefore()` and `isAfter()` - Checks if a date is before or after another date.

We can use `validator.js` functions in a `post` route to validate data that is sent inside a request. The following example extracts the value from a form named "email" and logs whether it is a valid email address.

```js
app.post('/submit', 
  (req, res) => {
   console.log( validator.isEmail(req.body.email)); 
})
```
If you’d like to run the application, type `node app.js` into the bash Terminal and then refresh the mini browser.



Input Sanitization: Validating Forms

Arguably, the best technique to protect against SQL injections is a method called prepared statements. Prepared statements are predefined SQL queries that take user input and place them into placeholders using array syntax. 

Consider this insecure SQLite database query that directly constructs the query string from user input:
```js
db.get(`SELECT * FROM Employee  WHERE FirstName = ${req.body.firstName} AND LastName =  ${req.body.lastName}`, (error, results) => {
  ...
});
```

The query can be reconstructed to use the following array syntax, where the first element replaces the first question mark, and the second element replaces the second question mark.

```js
db.all("SELECT * FROM Employee  WHERE FirstName = ? AND LastName = ? ", 
  [req.body.lastName, req.body.firstName], 
  (error, results) => {
  ...
});
```
This minimal change ensures that the input strings are properly escaped and special characters are removed, preventing SQL injection attacks. Let's try this out in our workspace!

In the workspace is a form where employees can input their Last Name and get their personal employee records. For example, an employee named "Andrew Adams" can input "Adams" to get their records.

A disgruntled employee discovered that they can get all the employee records by submitting malicious code into the input field.


Prepared Statements: Placeholders

Another aspect of input sanitization is data sanitization. Data sanitization is the process of removing all dangerous characters from an input string before passing it to the SQL engine. For example, we can remove unwanted characters or spaces that might lead to a SQL injection. `validator.js` includes functions to clean up data inputs. 

We can use `validator.normalizeEmail()` function to remove formatting on email inputs to remove potentially dangerous characters. For example:

```js
console.log(validator.normalizeEmail("     STUDENT@Codecademy.com"))
```

The above code will print out `student@codecademy.com`.

We can use the `validator.escape()` function to replace `<`, `>`, `&`, `'`, and `"` characters that could be confused with HTML entities. For example:

```js
console.log(validator.escape("1 < 2"))
```

The above code will print `1 &lt; 2`.

View the [documentation page](https://www.npmjs.com/package/validator) for a list of all sanitizer functions available.

We'll be using the same email and password forms and sanitizing their values.

Data Sanitization

SQL injections are common vulnerabilities that affect applications using SQL as their database language. Hackers can use their knowledge of SQL to construct text inputs that can trick an application and access information they shouldn’t have, change database records, or even take complete control of the system. There are some strategies to prevent SQL injections that can be employed in the back-end code such as *input sanitization* and creating *prepared statements* (also known as *parameterized queries*).

Throughout this lesson we will be examining those two techniques to protect against SQL injection attacks inside a Node.js application.


SQL Injection Protection Strategies

Great job! You now know some techniques to protect against SQL injections! SQL injection is a dangerous vulnerability and preventative measures should be implemented to make your database-driven apps safe and secure.

The main techniques we covered in the lesson are:  
* Input sanitization using `validator.js` to validate expected user inputs as well as to clean data inputs before sending them to the SQL engine.
* Using prepared statements with placeholders to ensure that SQL queries are properly escaped.

In the workspace to the right is a web form that takes an email address as input. When an email is submitted, the server will normalize the submitted email value using Validator.js and then use the value in a parameterized query. These steps will make the database query more flexible and secure by preventing SQL injections.

Review

Web forms present a security vulnerability on websites where hackers can potentially interact with a database. A seemingly harmless form or URL parameters can be a place for hackers to inject malicious code to a server. 

One method of preventing SQL injection is to *sanitize* inputs. Input sanitization is a cybersecurity measure of checking, cleaning, and filtering data inputs before using them. 

[validator.js](https://www.npmjs.com/package/validator) is a library of string validators and sanitizers that can be used server-side with Node.js. `validator.js` can be used to validate forms and sanitize inputs before using a form value in the application code.

Note that there is a package named [`express-validator`](https://express-validator.github.io/docs/) that wraps `validator.js` functions for use in `express` applications.

In the next exercises, we'll differentiate between input validation and input cleaning. We'll apply these techniques on a simple web app.
