If you've spent time managing Rock, you've likely explored its powerful customization features—creating custom pages, reports, dashboards, and more. Rock gives you complete access to your data, enabling endless possibilities.
But this power comes with a responsibility too. In Jurassic Park, Ian Malcolm's famous quote warns about doing something just because we can, without considering if we should. This applies to Rock customization too: we must ensure that we implement solutions the right way, not just the easiest way.
One example of the risk of "easy way" solutions is called SQL Injection. This is a type of vulnerability that's surprisingly easy to introduce without realizing it. The term might sound technical and intimidating, but understanding it is essential for every Rock Administrator. Let's break it down.
What is SQL Injection?
Imagine this: you're creating a template that allows any of your members to see the name and email of their spouse. After all, we want to make it easy for them to see when something needs to be updated, right?
One way of getting the information is using a SQL statement - directly accessing anything you ask for from the database. Fortunately, a basic SQL query is relatively easy to learn and the Rock community is always eager to contribute snippets and pointers to anyone who can explain the need.
So you add an HTML block to some public page to start building the query. Hardcoding Cindy Decker into the query is fine for now — you'll make it dynamic later. You enable the SQL command and use this template:
{% sql %}
SELECT
[NickName]
, [LastName]
, [Email]
FROM
[Person]
WHERE
[Guid] = 'b71494db-d809-451a-a950-28898d0fd92c';
{% endsql %}
{% for row in results %}
<p>{{ row.NickName }} {{ row.LastName }}: {{ row.Email }}</p>
{% endfor %}
Being a responsible administrator, you've avoided using PersonId since it's sequential and predictable, allowing anyone to cycle through IDs and extract contact info. Instead, you use a GUID, which isn't easily guessable. Running the template confirms that Cindy's details appear when the page loads.
But before proceeding, ask yourself: Can we trust this query to always do what we intend? SQL queries bypass Rock's security, granting full access to your database—including the ability to modify or delete data. If someone with bad intentions got involved, what would prevent them from altering this query to do harm?
“Ask yourself: Can we trust this query to always do what we intend?”
For this query, we'd want to make sure that only Rock Administrators can edit the block settings. Since the query is defined within the settings and doesn't rely on external inputs, we can trust it—assuming we trust everyone in that role. This makes it generally safe to use on pages where this information is needed.
Of course, we don't want everyone to see Cindy's information - we need people to see their own spouse's information. To make it dynamic, we'll update the query to do the lookup based on a URL parameter. The URL for such a page might look like this: https://www.rocksolidchurchdemo.com/spouseinfo?SpouseGuid=b71494db-d809-451a-a950-28898d0fd92c (no, this page doesn't exist in demo, but you can see what we're doing).
Here's the same template as we used above, but we swapped out the hardcoded GUID with some Lava that will get that text from the URL (I've highlighted the part that changed):
{% sql %}
SELECT
[NickName]
, [LastName]
, [Email]
FROM
[Person]
WHERE
[Guid] = '{{ 'Global' | PageParameter:'SpouseGuid' }}';
{% endsql %}
{% for row in results %}
<p>{{ row.NickName }} {{ row.LastName }}: {{ row.Email }}</p>
{% endfor %}
Now that we've made that change, we can change the URL to provide a different valid GUID, and it'll show us that person's information instead.
Again, we should ask ourselves Can we trust this query to always do what we intend?
Using a GUID instead of an ID helps prevent easy data scraping, but we've overlooked another risk: we're now letting a URL parameter (which anyone can modify) become part of our query. We assume the URL will always contain a valid GUID, but what if someone enters something unexpected?
That's the core of SQL injection—taking untrusted input and allowing it to become part of a query.
We always risk SQL Injection when we take untrusted input and allow it to become part of a query.
Consider this: what happens if someone sets SpouseGuid=' OR 1=1 --? It might look like gibberish, but plug it into your query like the Lava will, and watch what happens.
Your simple query now becomes:
SELECT [NickName], [LastName], [Email] FROM [Person]
WHERE [Id] = '' OR 1=1 --'
(The
-- starts a comment in SQL, preventing errors from the trailing
').
Yikes! Since we're injecting raw text into the query, that troublemaker just hijacked it to display every person in the database whose Id is '', or where 1=1. Since 1 is always equal to 1, our query is now exposing contact details for pastors, staff — everyone.
And it gets worse. Instead of just modifying the filter, what if they inserted an entirely new command, like: DROP TABLE [FinancialTransaction]?
That would erase all financial records — not just a bad day, but a disaster.
Protecting Against SQL Injection
Now since we see how trivial and powerful SQL Injection can be, you're probably plenty nervous about this risk. So let's talk about how to protect yourself from having this happen!
Option 1.
The most common way of avoiding this SQL-based attack would be to ... avoid using SQL! In many places you could use SQL, you could get the same data using Lava instead. In this case you could use a PersonByGuid Lava filter to get the person record instead of using SQL.
This does the same thing as the query above:
{% assign spouse = 'Global' | PageParameter:'SpouseGuid' | PersonByGuid %}
<p>{{ spouse.NickName }} {{ spouse.LastName }}: {{ spouse.Email }}</p>
Since Lava filters are read-only and respect entity security, the only risk here is if someone guesses or obtains another person's GUID. Even then, they'd only access that one person's phone number—there's no way to expand this into viewing multiple records.
Option 2.
In cases where using the SQL command is the best or only option, the safest way to handle untrusted input in your query is through parameters. These are covered in the SQL Command documentation under the SQL Parameters heading.
For example, instead of inserting user input directly, you assign it as a parameter:
{% assign spouseGuid = 'Global' | PageParameter:'SpouseGuid' %}
{% sql SpouseGuid:'{{ spouseGuid }}' %}
SELECT
[NickName]
, [LastName]
, [Email]
FROM
[Person]
WHERE
[Guid] = @SpouseGuid;
{% endsql %}
{% for row in results %}
<p>{{ row.NickName }} {{ row.LastName }}: {{ row.Email }}</p>
{% endfor %}
This works because the untrusted input never becomes part of the query itself. Instead, it acts as a reference—telling SQL to return records where the GUID matches the given value. An attacker's input can't escape the variable, meaning an injection attempt like ' OR 1=1 -- simply returns zero results.
Option 3.
The third option if the above two are absolutely unavailable, is to rely on the SanitizeSql Lava filter to make any untrusted input safe. Ideally, convert those values into SQL variables as well or use this within a SQL parameter like above too.
At a minimum, if you have to use Lava directly in your query, it would look like WHERE [Guid] = '{{ 'Global' | PageParameter:'SpouseGuid' | SanitizeSql }}'
Usually if the value should be a specific data type, the best approach is to sanitize the value first, then let SQL enforce the type. This ensures that invalid input either gets converted correctly or returns a blank result, preventing potential exploits.
Here's how that looks if we expect a GUID value:
{% sql %}
DECLARE @SpouseGuid UNIQUEIDENTIFIER = '{{ 'Global' | PageParameter:'SpouseGuid' | SanitizeSql }}';
SELECT
[NickName]
, [LastName]
, [Email]
FROM
[Person]
WHERE
[Guid] = @SpouseGuid;
{% endsql %}
{% for row in results %}
<p>{{ row.NickName }} {{ row.LastName }}: {{ row.Email }}</p>
{% endfor %}
(UNIQUEIDENTIFIER is what SQL Server calls a GUID).
If your expected value was an integer (whole number) then you could use DECLARE @myId INT = '[your lava]' and that will make sure that there aren't any non-integer characters like ' or -- that would allow the value to change the nature of your query.
Conclusion:
SQL Injection is a very real risk, any time you are using something external as part of your query. But now you know how to spot it, and you've got some tools to protect your queries if Lava just won't do.
If only this school had a developer with your knowledge!