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 database to the internet.
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 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>
<script>alert("I am evil")</script>
</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 should 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,