SQLPage's security guarantees

SQLPage is a tool that allows you to create a full website using only SQL queries, and render results straight from the database to the browser. Most programmers, hearing this, will immediately think of the security implications of this model.

This page is here to provide a list of the security guarantees that SQLPage provides. SQLPage was designed from the ground up to be usable by non-technical data analysts and other non-web-developers, so it provides safe defaults everywhere, so that you don't have to think about basic security issues you would have to worry about in a traditional web development stack.

SQLPage does not expose your database to the internet

SQLPage websites are server-side rendered, which means that the SQL queries stay on the server where SQLPage is installed.

The results of these queries are then rendered to HTML, and sent to the user's browser. A malicious user cannot run arbitrary SQL queries on your database, because SQLPage does not expose your entire database to the internet, only the results of your prepared queries, rendered as web pages.

Protection against SQL injections

SQL injections are a common security vulnerability in traditional back-end web development, that allow an attacker to execute arbitrary SQL code on your database.

SQLPage is immune to SQL injections, because it uses prepared statements to pass parameters to your SQL queries.

When a web page starts rendering, and before processing any user inputs, all your SQL queries have already been prepared, and no new SQL code can be passed to the database. Whatever evil inputs a user might try to pass to your website, it will never be executed as SQL code on the database.

SQLPage cannot execute any other SQL code than the one you, the site author, wrote in your SQL files.

If you have a SQL query that looks like this:

SELECT * FROM users WHERE userid = $id;

and a user tries to pass the following value to the id parameter:

1; DROP TABLE users;

SQLPage will execute the search for the user with id 1; DROP TABLE users; (and most likely not find any user with that id), but it will not execute the DROP TABLE statement.

Protection against XSS attacks

XSS attacks are a common security vulnerability in traditional front-end web development, that allow an attacker to execute arbitrary JavaScript code on your users' browsers.

SQLPage is immune to XSS attacks, because it uses an HTML-aware templating engine to render your SQL query results to HTML. When you execute the following SQL code:

SELECT 'text' AS component, '<script>alert("I am evil")</script>' AS contents;

it will be rendered as:

<p>
    &lt;script&gt;alert("I am evil")&lt;/script&gt;
</p>

Additionnally, SQLPage uses a Content Security Policy that disallows the execution of any inline JavaScript code, and only allows loading JavaScript code from trusted sources.

If you have some legitimate JavaScript code that you want to execute on your website, you can use the javascript parameter of the shell component to do so.

Authentication

SQLPage provides an authentication component that allows you to restrict access to some pages of your website to authenticated users.

It also provides useful built-in functions such as sqlpage.basic_auth_username(), sqlpage.basic_auth_password() and sqlpage.hash_password() to help you implement your authentication system entirely in SQL.

The components and functions provided by SQLPage are designed to be used by non-technical users, and to respect security best practices by default. Passwords are hashed with a salt using the argon2 algorithm.

However, if you implement your own session management system using the cookie component, you should be careful to follow the OWASP session management best practices. Implementing your own session management system is not recommended if you are a non-technical user and don't have a good understanding of web security.

Protection against CSRF attacks

The recommended way to store session tokens for user authentication in SQLPage is to use the cookie component.

All cookies set by SQLPage have the SameSite attribute set to strict by default, which means that they will only be sent to your website if the user is already on your website. An attacker cannot make a user's browser send a request to your website from another (malicious) website, and have it perform an action on your website in the user's name, because the browser will not send the cookies to your website.

SQLPage differentiates between POST variables (accessed with the :variable syntax), and variables that can come from URL parameters (accessible with $variable). Note that URL parameters prefixed with _sqlpage_ are reserved for internal use.

When a user submits a form, you should use POST variables to access the form data. This ensures that you only use data that indeed comes from the form, and not from a URL parameter that could be part of a malicious link.

Advanced users who may want to implement their own csrf protection system can do so using the sqlpage.random_string() function, and the hidden input type of the form component.

For more information, see the this discussion.

Database connections

SQLPage uses a fixed pool of database connections, and will never open more connections than the ones you configured. So even under heavy load, your database connection limit will never be saturated by SQLPage.

And SQLPage will accept any restriction you put on the database user you use to connect to your database, so you can create a specific user for SQLPage that only has access to the specific tables you will use in your application.

If your entire application is read-only, you can even create a user that only has the SELECT privilege on your database, preventing any accidental data modification. SQLPage will work fine with such a user and will never try to execute any other SQL statements than the ones you explicitly wrote in your SQL files.