SQL Parameters and Injection Protection

In This Lesson

We'll learn about SQL Parameters, also called query bindings, and how using them helps safeguard our database from malicious attacks attempting to perform SQL Injection.

Created by: @tomgobich
Published: 1 year ago

Join the Discussion 0 comments

Be the first to comment!

Transcript

Let's circle back to the query builder that we were working with. So we can use raw queries with those as well,
whether that be a straight up raw query builder or raw where statements or raw selects, things like that, we can use raw SQL where we need to.
But whenever it comes to using raw SQL, we wanna make sure that we're parameterizing our values so that we're safeguarding our queries from SQL injection
and SQL attacks. For example, if we were to jump back into the documentation here, we go down to the raw query builder. By parameterization, what I'm talking about
is this bindings section. Essentially we pass values not through the raw SQL string, but rather through a parameter argument.
So for example, with the bindings approach, we use question marks to specify where we want parameters to reside, and then we can provide the values as the second argument
to the raw query in an array. And should the column itself need to be dynamic, you can use double question marks to specify that something needs parsed as a column name.
So for example, if you wanna do select star or select all columns from the users table, where users.id equals one, you could use the double question mark
to specify that this parameter position is a column name, and then traditionally the single question mark will reside as a value, and that's where the one would get injected,
resulting in this final SQL query here. And this parameterized approach protects our database against malicious users running SQL injection attacks
against our database. For example, somebody could come along to one of our filter inputs, provide SQL that would drop all of the tables within our database,
and if we weren't using parameterization for that particular filter input, that SQL to drop all of our tables could actually run
if we're injecting it straight into the raw SQL statement that we are running for that filter value. Whereas if we were to provide that malicious SQL in as a parameter value,
Lucid's safeguards will come into play and won't actually allow it to run with the raw SQL for that statement. So when you're accepting user input, you always want to make sure that those values
are provided into your SQL as parameterized values so that you have that safeguard in place and so that malicious users can't come along and screw up your database.
And if you're not a big fan of the question marks, there's also a named placeholder approach that you could do as well, and this looks relatively similar to route parameters.
So you just do colon and then a parameterized name, and then you can provide an object in as the second argument instead of an array with a key value pair where the key is the parameter name
and the value is the SQL value. If you then suffix the parameter name with another colon, you can do the same thing as the double question mark
and specify that this parameter name is intended to be a dynamic column. So here the name for the parameter is column. It's got a colon before and after,
and that allows us to specify a key of column with the actual raw SQL column that we want to be provided in place of this parameter within our raw SQL.
They provide another great example that would come into play whenever you're doing dynamic joins, where you might have more than one column. So you have column one and column two,
parameter column one and parameter column two in here to do a likeness check on your inner join. And the next one is designation between just raw and raw query.
So far the examples that we've been looking at have been for raw query. These allow us to actually run and execute raw query statements as they're defined in our database.
The db.raw is essentially meant to be referenced elsewhere via another SQL statement. So we can use db.raw to execute kind of sub queries
and then db.rawQuery to execute raw queries as a whole. So first I want to mention that the where statements and whatnot that we were working with a couple of lessons ago,
whenever we were taking a look at the model query builder and the db.queryBuilder, those where statements do parameterize the values. So for example, let's see, that was the select query builder here.
This where statement right here, you have the column name and then you have the value. This will take care of that parameterization for these values.
So we have that SQL injection protection already baked in whenever we use these particular methods. It's just those raw ones that that really comes into play. We haven't quite covered validation yet,
but it's also important to note that you always want to validate user data coming into your server as well to make sure that it matches what you're actually expecting a value to be. And we'll get into validation
whenever we start specifically working with forms as that's where user provided data kind of starts coming into play there. So let's go ahead and hide our browser away, jump back into our terminal,
and let's take a look at a raw query with our particular database. So we'll do node.ace.repl to jump into our REPL session. We will await.load.db,
and let's also go ahead and await.load.models as well. So first let's start by taking a look at a raw query using the database module.
So await db.rawQuery, and let's just say that we want to select star from movies, where some column,
and let's take a look at the question mark approach first. So we'll do question mark, question mark for where some column equals question mark. So some value, okay?
And now we need to provide in a second argument, which is an array, and we can say where id is providing a value of one.
So when we run this, scroll up a little bit here, we're going to get back a number of information from our query. So let's keep scrolling up. There we go. Here's the rows.
So the rows contain the underlying data. Everything else surrounding this is just information about our query and the return fields.
So you can see that we get back our movie with an id of one, and the title is, "If You Leave Me Now." So everything with that query seemed to work a-okay. For sanity's sake, we can go ahead and run it again
with the id of two, and we should expect a different result. We can go ahead and just wrap this up and hone in on rows to simplify and get rid of all of this extra information
that we don't care about at this particular point in time. So let's go ahead and run that, and there we go. So now we get back an array with an object of an id of two, and the title is, "I Kissed a Girl."
Everything seems to be working a-okay there. Let's take a look at the other raw query approach. So let's await db.rawQuery,
select star, keep doing an ampersand instead of a star, star from movies where, and let's do column,
start that both with a colon and end it with a colon, equals colon id, cool. And now we can provide an object where column is id,
and id, or that could be a little confusing. Let's replace our id with a value there. So now we have a column in value parameter
where our column parameter expects a table column, and our value expects that column's value. So we'll provide a value here, and let's do three.
So we should get yet another movie. We have this wrapped in parentheses, so I'll do another end parentheses followed by rows, hit enter there. Oh, whoops, never ended our string.
Okay, let's end our string there. Okay, now let's run that. There we go. So now we get back our movie with an id of three, and title, "Nothing Compares to You." Cool, so everything seems to be working a-okay
with those raw queries, and there's examples of how we can build them out and use them. So you could really provide any raw SQL that you need to in this approach for as much information as you need,
and the raw response will be returned back to you. Just make sure that you always parameterize your values to keep your SQL safe from SQL injection.
And we can await models.movie.query.where raw
to use a raw where statement within this particular query. And we can do the exact same thing here as well.
So we could do where question mark, question mark equals question mark in the same way that we were originally with our raw query statement right up here
to provide an id and two as our parameterized values. So we'll provide it an array as the second argument there. And I'm gonna end this with Poyo
just so that we can see what the actual results are. Our first argument here would then be id just as it was before. If we can go back to using one as our id value.
We hit enter here. Look at that, we get back our movie with an id of one. If we know that this column here is always going to be id, we can just hard code that in since that's not user provided,
and then just provide in one as the underlying value there just like so. This should also support the exact same naming structure that we were using before with the raw query.
So colon parameter name colon to specify that we can accept in some column as a parameter value. And then we could do a parameter name.
So value for the underlying expected value for the column. This would switch to an object where the first key would be our column.
So column, and then we would want to query against our id in this particular case, where the value would search for an id of two. So we can hit enter there.
And look at that, we get back our movie with an id of two. And this parameterized syntax should work the same for all of the raw SQL statements that you can use throughout AdonisJS.
And if you're ever wondering what raw statements you have at your disposal, you can see that within the select query builder and raw query builder statements. Right down here, we're taking a look at where raw,
which is the execution that we just used. They do have an important distinction here on the documentation that we've been covering throughout this lesson. Don't plop your values straight into your query. Instead, use parameterization
to make sure that you have that SQL injection protection. But if you scroll through on the right hand side of here, you should see all of the different options that you have for raw statements. So like here, you had that where raw,
if we continue onward, having raw, group by raw, order by raw, all of the parameter arguments should work the exact same way.

Let's Learn AdonisJS 6

113 Lessons

14h 15m

Introduction

Fundamentals

Building Views with EdgeJS

Database and Lucid ORM Basics

Lucid ORM Relationships

Working With Forms

Authentication & Middleware

Filtering and Paginating Queries

User Watchlist

User Profiles

Admin Panel

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.