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.
- Author
- Tom Gobich
- Published
- Mar 11
- Duration
- 9m 19s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
SQL Parameters and Injection Protection
-
(upbeat music)
-
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.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 7s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!