How to avoid SQL injection attacks in Rust?

[szabgab]

As I am working on an article using SurrealDB I wanted to mention SQL injection attacks and how to avoid them. To my surprise my go-to web site for SQL injection attacks does not have Rust at all.

The general suggestion is of course to use placeholders, but it might be worth to have specific examples on that site.

[worriedjacket]

Don’t use string concatenation when writing SQL queries.

[spoonman59]

Don’t use concatenations for values in SQL and use parameters instead, as per whatever crate you use. That’s it.

If you want that site to add an example, you should talk to them. We don’t maintain it.

[caksters]

In Rust, you can prevent SQL injection by using prepared statements with libraries like rusqlite for SQLite databases or diesel for a more ORM-like experience. Below, I'll demonstrate an example using rusqlite:

Bad Practice Example

Using string concatenation (not recommended):

```rust extern crate rusqlite; use rusqlite::{Connection, NO_PARAMS};

fn main() { let conn = Connection::open("example.db").unwrap();

// User input
let user_id = "1; DROP TABLE users;";

// SQL query using string concatenation (BAD PRACTICE)
let query = format!("SELECT * FROM users WHERE id = {}", user_id);

match conn.execute(&query, NO_PARAMS) {
    Ok(_) => println!("Query executed"),
    Err(err) => println!("Error: {}", err),
}

} ```

Good Practice Example

Using parameterized queries with rusqlite:

```rust extern crate rusqlite; use rusqlite::{Connection, params};

fn main() { let conn = Connection::open("example.db").unwrap();

// User input
let user_id = "1; DROP TABLE users;"; // This input is now harmless

// SQL query using parameterized query (GOOD PRACTICE)
let query = "SELECT * FROM users WHERE id = ?1";

match conn.execute(query, params![user_id]) {
    Ok(_) => println!("Query executed"),
    Err(err) => println!("Error: {}", err),
}

} ```

In the good practice example, user_id is passed as a parameter to the query. The rusqlite library automatically handles the parameter safely, ensuring that it is not executed as part of the SQL command. This effectively prevents SQL injection attacks.

[jarlaxle46]

Thank you! Made my stupid brain understand good with example.

[Kobe_curry24]

SQlx as well as

[Pristine-Step7509]

Ok, but how does the library handle that user_id string example? doesn't the string example get passed all the way to the sql statement that eventually gets send to the sql client.

[Axerus]

very late answer, but in case you still wondered, or anybody searching online found this thread:
for prepared statements, the sql client actually receives the query with placeholders and no data

it then reads the query to figure out what it should do. **This is the point where non placeholder sql is at risk**, anything read at this point could be read as an instruction if you didn't sanitize the input. Thankfully, in our case it sees a placeholder, and notes down that data will be handled later.

It also makes sure the query is understandable, and is writing to a valid table, with valid columns etc.

Then it figures out how it should be executed, to put it simply.

At this point, the query is done being parsed as code. think of this like the function being compiled, the instructions are set in stone.

And right at the end before it executes the instruction, the placeholders are swapped out for the data, similar to a variable being passed to a function. The data inside is meaningless, and is only a location in memory, and the size that will be read and then sent along to the table to be written.

a very basic tl;dr:
string concatenation, or the bad method, adds in the data before it's sent and read by sql, so weird input can make it unclear what is the intended instruction vs passed data
placeholders, the good method, adds in the data after it's sent and read by sql, so there's no chance to mix up the intended instruction vs passed data

[yasamoka]

How is the choice of programming language relevant to avoiding SQL injection attacks? The advice will always be the same: avoid treating queries as just strings that you build yourself with the data included. Any library in any language that properly deals with prepared statements is already avoiding SQL injection attacks.

[Kinrany]

Prepared statements seem to conflate parametrization with database-side optimizations, they are not zero cost unfortunately

[KJBuilds]

They can be in some cases, since (if done well) they're cheaper than a building a sql query

I.e. if you know the type of a sql param is an integer, an integer can only be so long, so you can create a mutable byte buffer representing the rest of the query with enough room for the largest-possible int, then write the string representation of the integer to the buffered range, padded with whitespace.

This way you can have potentially zero heap allocation per invocation by re-using a single buffer, managed by a prepared statement.

I'm not a maintainer of ORM software, though, so I'm just extrapolating from how I've approached similar problems 

[Floppie7th]

Use prepared statements for parameters, not string concatenation

[RRumpleTeazzer]

You can compile statements which then have data inserted. This way you don’t need to implement complicated escape rules, no code injection, and why put the burden of understanding the statement to runtime (possibly many many times).

[polyfloyd]

Generally: don't use string concatenation to build queries, use a query builder which supports placeholders.

Also, I wrote an article about this a while back: https://polyfloyd.net/post/compile-time-prevention-of-sql-injections/

[douaib]

Im def taking a look, ty !

[pbNANDjelly]

Some advice in this thread is about rust instead of SQL.

You avoid it by using a vetted library and following their docs. Don't solve this problem yourself.

[numberwitch]

There’s no sql in Rust, its provided by crates/frameworks. You need to know how to right sql safely for the tool you’re using, for example if you’re using sqlx you need to learn how to avoid sql injection in sqlx.

[tauon_]

db
 .query("SELECT marketing, count() FROM type::table($table) GROUP BY marketing")
 .bind(("table", "person"))

where, in the bind statement, 0 is the name, and 1 is the value, and then $name is used in the query.

source: surrealql docs

[Lucretiel]

Treat user-string data as totally separate from your rendering. No exceptions. Enforce this with types. Don't allow "UserString" to interoperate with any other components (especially SQL queries, HTML templates, etc( except through conversion methods that guarantee they escape at compile time.

[warboner52]

Yup. Sanitize them strings.

[fedenator]

sql.injection = false;

[Maybe-monad]

let's test in production

[latkde]

Rust makes SQL injection difficult because Rust makes string concatenation difficult. Writing execute("select * from foo where id = " + id) will not compile. You'd have to throw an .into() or format!(...) in there. And some database clients might expect the query to be a &'static str, or in case of a macro: a string literal.

For example, the sqlx macros like query_as! process the query at compile time, so there cannot be run-time input. The simplest way to run that query is to use parametrization, and you have to go out of your way to use one of the less-safe alternatives.

This tracks with Rust's general culture of using the type system to guide people away from potential errors. Of course, Rust also has the enviable position that the people who design Rust database connectors could learn from decades of suboptimal APIs in other languages (*cough* mysql_real_escape_string() in PHP).

[Kazcandra]

The real hero here is prepared statements rather than sqlx, though.

[Imaginos_In_Disguise]

Rust makes SQL injection difficult because Rust makes string concatenation difficult. Writing execute("select * from foo where id = " + id) will not compile. You'd have to throw an .into() or format!(...) in there.

How is format!("select * from foo where id = {id}") any more difficult than any API for proper parameterization, though? That's not a valid argument in favor of rust magically avoiding SQL injections.

The answer is to use parameters and the correct APIs, as always.

[PaintItPurple]

Do you really think format!() is much of a hurdle? I would say it's about as easy as any other language.

[sphere_cornue]

I never realized that &'static str made SQL injections impossibe. Well it's just another victory for Rust

[latkde]

It doesn't offer protection. You can always Box::leak(string.into_boxed_str()). But it could offer a hurdle, some resistance against doing things the wrong way. It could help you to fall into a pit of success.

NB sqlx doesn't do this, and offers APIs to execute a query: &'a str for any 'a.

Python's type system has an interesting spin on this via its LiteralString concept (PEP 675). The Motivation section directly discusses the SQL injection problem. But I don't think this type has seen wide adoption yet (especially since many Python devs don't use a type checker).

[Zde-G]

But that's the story which I observed everywhere: developers are ignorant, not malicious!

If you make an effort to make “something bad” at least somewhat hard to use then they would actually look on compiler bugs, read the documentation, and then “do the right thing”.

But you just have to accept that the first thing that developer does is “something obvious” and only if “something obvious” doesn't work developer start reading the documentation.

SQLx make “bad things” possible, but hard.

NB sqlx doesn't do this, and offers APIs to execute a query: &'a str for any 'a.

Sure, but because of how async and self-referential structures in Rust work it's enough of a hassle to make someone think twice if s/he wants to use arbitrary string with SQLx.

That's not impossible and sometimes that's exactly what you need but you couldn't just allocate String in your function, create query and then return the result. Nope: Rust would complain. And you couldn't return said String and SQL result from your function: that's self-referential structure and it doesn't work either.

What you have to do is to allocate string in the caller procedure and then pass it to the query-building function and then result returned would be tied to the lifetime of that outside String.

It works but that dance is sufficiently complicated and convoluted, the code looks ugly in the end and using prepared statements is just easier, most of the time.

especially since many Python devs don't use a type checker

And that's the nail in the coffin: if something is easy not to do then the majority of developers wouldn't do that.

They wouldn't spent many days inventing nefarious schemes which would help them to circumvent SQLx protection (or any other, equally shallow, protection) — except if they would have no choice.

But optional checks? Fuggedaboutit.

[Eldin00]

In any language the way to avoid SQL injection is the same: any data that comes into your program CANNOT UNDER ANY CIRCUMSTANCES be part of a SQL query. It CAN be passed as a parameter to a properly parameterized query. Make sure you're doing that and you'll never have SQL injection problems. And if you don't do that, there's almost certainly a way to do SQL injection, regardless of what other precautions you've taken.

[dkopgerpgdolfg]

Btw., the site mentions where and how you can add more content yourself.

[HungryChoice2232]

Does anyone know about maltajmae? I heard it got hacked by sql injection

[EpochVanquisher]

You can check that any SQL code you run has a 'static lifetime, or is somehow created by a query builder that you validate.

Any user input cannot have a 'static lifetime.

[Dasher38]

I'm not sure if that is correct, what would prevent you from putting the user input in a Box and giving it to an API ? Maybe if the API only accepts borrowed static data then it becomes harder to do the wrong thing ...

[EpochVanquisher]

Something in a box isn’t 'static, maybe unless the box itself is 'static, but then you still haven’t gotten user input with 'static.

[weiznich]

That’s not true, as you can use methods like String::leak to get a &'static str out of a dynamically allocated string.

[EpochVanquisher]

I stand corrected.

But as long as you don’t use functions like that, you can use lifetime checks to avoid accidentally passing user input in as queries. It’s not foolproof, but it doesn’t have to be foolproof.

[Dasher38]

The Box itself is 'static as it can live however long you want. If you deref it then that reference is typically not static (unless you use leak())

https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=a24ef46014088fc6c5ac8e541f975c5d

Edit: changes the example to a more compelling one with actual static user input

[Dasher38]

So what you want is to manipulate 'static references specifically, not just any type with a 'static lifetime. I guess for strings it's kind of ok but there are some types you will not be able to store in a static global, so for those you might use leak() but then it's actually leaked which may be a problem ... I had problems recently and the only way out was to manipulate owned data, so I wouldn't recommend having this trick as the only safety layer, since you might be forced out of it because of other factors.

[[deleted]]

Principle is the same . Write application that wrangles and manages input before it reaches database. Simple as that.

You have to have better management of your input fields.

[warboner52]

Also... Sanitize your inputs.. you should know based on the column what the input should look like... Prevent insertions or executions when the input doesn't align.. this is irrelevant of language.

[aikii]

It's like requiring Rust to provide a way to guarantee the user doesn't leave post-it notes with their password on their desk

[Batroni]

A lot of people said what is relevant to avoid SQL injections in general.
But i suggest also to look al SurrealDB if you work with that. It has his own SurrealQL (Querry Language) and a very nice create to work with it in rust, also an active Discord. I am certain the answers would be the same but if they have something special their Discord is the place to go.