Compile Time Prevention of SQL-Injections in Rust | Hacker News Reader

Compile Time Prevention of SQL-Injections in Rust

polyfloyd.net

56 pointsjesperht49 comments

This doesn't actually work. It is possible to produce objects with 'static lifetime references at runtime.

What &'static means is that whatever the reference is pointing at will never be modified or go out of scope. One way to provide this is to put it in the read-only part of the executable, which is what literals do. Another is to use into_boxed_str() [1] and Box::leak() [2] to leak the string and thus make sure it will never be modified or freed. Neither function is unsafe, while Box::leak() is still only in nightly.

[1]: https://doc.rust-lang.org/std/string/struct.String.html#meth... [2]: https://doc.rust-lang.org/std/boxed/struct.Box.html#method.l...

Good clarification. Although it's at least likely to stop you from using user input accidentally. I think it's unlikely that someone would use either of the options above when constructing a string involving user input without knowing what they're doing.

This does seem to be the case. Here is an example I was able to create:

  use std::io::stdin;

  fn main() {
      let mut s = String::new();
      stdin().read_line(&mut s).expect("Did not enter a correct string");
      let sql_example = format!("SELECT * FROM users WHERE username={}", s);
      let x = Box::new(sql_example);
      let static_ref: &'static str = Box::leak(x);
      println!("{}", static_ref)
  }

Note the type on our variable "static_ref". It is a static str, meaning it could be an argument to the code in the blog post. No "unsafe" blocks either.

When I run it

  Input: ' OR '1'='1
  Output: SELECT * FROM users WHERE username=' OR '1'='1

The technique might be still useful if it was only allowed in debug builds to reduce boilerplate during debugging in a system that tried to use types to solve the issue.

This is a pretty natural thing to want, but is not actually usable in real world settings.

If your SQL has to be computed at compile time, how do you implement any sort of search where you will have a variable number of ANDS & ORs?

You could try to create some sort of SQL-builder API where each fragment must be static, and each fragment contains its own placeholders.

Then you can use conditionals and loops to decide which fragments are included.

Or you can just use runtime SQL injection prevention methods?

Or just have an employee carefully monitor user input manually, and have them propagate it to the backend with a delay.

That is, sure, it's not like Rust takes the option to check for injection at runtime.

Our team found it's easier to just not have any users.

I find that not having any users helps a lot with load balancing also.

Why "just" ? It's incredibly more costly than having the injections being prevented at compile-time.

I think the appeal of sql builder apis is that they make it much harder to shoot yourself in the foot. If your queries are strings you can use placeholder syntax to avoid injection but all there is nothing stopping you (or a junior dev) from writing something stupid like "and name ='" + username + "'". If queries are represented with a separate datatype then it is much harder to put unsanitized user input strings inside them.

>If your SQL has to be computed at compile time, how do you implement any sort of search where you will have a variable number of ANDS & ORs?

It's not your SQL (e.g. whole query) that needs to be computed at compile time, but the proper escaping of placeholder parameters.

So, the answer is: trivially.

In many cases that may be true, but not in the case of the parent comment you're responding to. The parent identifies a scenario where you have a faceted search functionality that generates conditions in the where clause of the query dynamically).

So? What prevents those conditions to be generated as a number of SQL fragments that are statically checked for injection?

What would be checked by Rust is the actual fitness of the fragments, not if your building them in a loop from pre-made WHERE, AND etc clauses + placeholders.

Definitely possible. Just more nuanced than the approach suggested in the article.

As a non-webdev who only have a surface-level understanding of SQL: is that a thing people do regularly? Like, you literally append a bunch of strings together dynamically every request to construct queries?

It is quite common to build up a query. You can and should still use parameters, but I have lost count of the times I have seen code like this:

where = "1=1";

if (cond) { where += " and foo ='bar'"; }

...

I'd imagine it would depend entirely on the type of search you're trying to do.

If you know all the possible ANDs and ORs beforehand, you could pass in flags to select which ones to use. That would also mean you could prepare queries ahead of time, and not need to compile them every time you run them.

I don't think there would be many situations where you wouldn't know what possible filters you could have at compile time, and in such a situation I'd question whether there was a better way to implement what I was trying to do.

I think the main problem (don't know whether this is possible in Rust which I am not familiar with) is that untrusted input is passed around as strings at all (Java, which I am familiar with, does this). I'd prefer:

- Getting untrusted input as a separate type

- Having only a controlled way to put instances of this type into an SQL query

You pretty much just nailed exactly what this post is demonstrating about Rust. The lifetime associated with the different types indicates the provenance of the variables.

Basically, the 'static lifetime guarantees that the query string was built at compile time. And then it allows the parameters to be from user input. To Rust, these are effectively different types due to the restrictions on the function definition, which restricts the first parameter to 'static, and the list of SQL parameters can come from anywhere (static or dynamic runtime).

Yeah, but using "built at compile time" as a synonym for "safe" is pretty crude. A runtime string composed of the concatenation of two compiled strings is still safe, yet not allowed here.

That seems easy enough to fix, no? Change the API to allow a runtime generated list of compile-time strings, et viola. Though, I doubt that's as safe as you might imagine. Imagine a return oriented programming-like technique using static SQL strings. Difficult, for sure, but not impossible.

This is doable in rust too.

It's a neat little hack, though it seems a bit restrictive. I was expecting a blog post about how, by using ownership, it should be possible to create an API that both requires untrusted data to be escaped and prevents double-escaping (though you could probably achieve pretty much the same in any statically-typed language).

Is it me or the person that wrote that post has no idea what SQL inject actually is?

I don’t understand at all what the difference is between the two functions.

The second function only accepts strings defined at compile time, meaning it can't be called with strings created at runtime (i.e. any string containing user input).

So what to you do if you need to make a sql call based on user input?

Use parameters, of course. Using SQL parameters for untrusted input is the only sane way to avoid SQL injections.

just add "to_owned()" and problem solved. This function doesn't protect nothing, it's a bullshit. I love Rust, but author is far from the theme he is trying to describe. And theme is dangerous enough.

to_owned() converts to a String, not to a &'static str. Those are not the same. You can't create a &'static str dynamically (though you can mutate one using unsafe.)

lol, so you call your SQL just with constants? "username" in his example just for one user forever? Still a bullshit.

No, only the SQL statement has to be 'static. It's not bullshit.

Can you provide real-world example please?

From TFA:

let _rows = sql_query("SELECT * FROM users WHERE username=?", &[username]);

The statement is static, but the [username] part is not, it's just a variable that can have whatever username you want.

and I already told that this example has no meaning, it's just a bullshit - nobody will use it in real app. You want to fetch from db info about the only one user, who's name is a constant? Yeah, all apps do it every day.

You can convert a `String` into a `&'static str` using only safe stdlib functions via `Box::leak(s.into())`. This uses `unsafe` internally, of course... but so does almost any code.

Ah cool, I hadn't heard about Box::leak until now. Coming to stable in 1.26 it seems.

The idea seems to be that if a variable has a certain kind of Rust lifetime, then it contains only trusted data associated with the program itself, rather than some untrusted input.

Problem is, that is too limiting to satisfy the functional use cases in a lot of code that constructs SQL queries by substituting values into templates, where some of the pieces are necessarily dependent on inputs from logged-in users and such.

Looks correct to me. What issue do you have with it?

They're saying injection attacks come from dynamically building strings, so if you prevent that you stop the vulnerabilities.

SQL Inject is not only a ' character. SQL Inject could be a blind query, subselect attack, time attack and so on and so on. This completely fails to prevent pretty much anything except the very naive and silly attacks.

This prevents dynamically created queries using lifetimes. It does not search for ' characters.

Yup. But it does it at compile time for basically no work.

No it doesn't.

The argument isn't "block ' characters to stop SQL injection" it's "force the developer to use prepared statements and no dynamically constructed queries".

First, no, it prevents much more attacks.

Second, the naive and silly attacks are the default definition of SQL injection -- and the most commonly found holes, so even if it just prevented those that would be still a huge help. Nothing statically ensures that at runtime except the programmer's due diligence.

Is this serious? It seems to prevent SQL injection by only allowing statically defined strings to be interpolated. So basically not allowing any kind of dynamic or user input.

That’s the point. You should never use string interpolation with strings defined at runtime for SQL.

Always build your queries out of strings defined at compile time and use them as prepared statements with parameters.

That is the theory, sadly it doesn't work everywhere on the SQL statement.

So we always end up with a mix of prepared statements and string manipulation.

It works by only allowing statically defined strings to be used for SQL /statements/, it still works for dynamic input.

https://en.wikipedia.org/wiki/Prepared_statement

The ? is a placeholder for dynamic values, the user input is bound to it.

Reply on news.ycombinator.com