How to Prevent SQL Injections with User-Search-Terms in Vapor 4 (Fluent 4)

How to prevent SQL Injections with User-Search-Terms in Vapor 4 (Fluent 4)

Since you are using Fluent, SQL injection is prevented automatically and you are good to go!

Instead of simply constructing a query like this:

SELECT * FROM machines WHERE name = '\(user_provided_name)'

Fluent uses value binding, which is a feature provided by databases to pass values into the query so that the value is escaped and won't be executed if the string contains SQL code. It looks something like this:

SELECT * FROM machines WHERE name = ?

And then the values are passed to the database server (MySQL in this case) with the query, where it automatically replaces the placeholders (?) with the values provided.

A quick comment on your query, if you want, you can import the FluentSQL module and then write your query like this:

.filter(\.$name ~~ term)

If you would rather leave it the way you have it now, that's fine also.

Vapor 4 case insensitive query

The equivalent in Vapor 4 is:

func queryUserMovies(_ req: Request) throws -> Future<[Users]> {
    let title = try req.query.get(String.self, at: "title")
    return Movies.query(on: req.db)
        .filter(\.$title, .custom("ilike"), title)
        .first()
        .unwrap(or:Abort(.notFound, reason: "There's no movie"))
        .flatMap{ movie in
            return movie.$users.query(on: req.db).all()
    }
}

You can even perform a wider search to find anything containing that title:

.filter(\.$title, .custom("ilike"), "%\(title)%")

Avoiding SQL injections with prepare and execute

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user='?'");

This won't work because it's searching for a literal '?' character — not a parameter. If you try to send a value for the parameter, MySQL will be like, "what do you want me to do with this?" because the query has no parameter placeholder.

If you want to use a parameter, you must NOT put the parameter placeholder inside string delimiters in the SQL query, even if the parameter will take string or datetime value:

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user=?");

The next example:

$sql_query = "SELECT * FROM Users WHERE user='" . $user . "'";
$dbh->prepare($sql_query);
$dbh->execute();

That will run the query, but it's NOT safe. You can prepare any query even if it has no parameters.

Using prepare() is not what makes queries safe from SQL injection. What makes it safer is using parameters to combine dynamic values instead of doing string-concatenation like you're doing in this example.

But using parameters does require the use of prepare().

PS: You don't need to put ; at the end of your SQL queries when you run them one at a time programmatically. The separator is only needed if you run multiple queries, like in an SQL script, or in a stored procedure. In your examples, the ; is harmless but MySQL doesn't require it, and it will just ignore it.

Are Codeigniter - Active Records Vulnerable to SQL Injections?

There are 2 safety features already provided by CodeIgniter for your case.

1. XSS filtering for your user input :
   Input Class has second boolean parameter for its methods, which lets you run the input through a XSS filter in case you do not have global xss filter on.

2. For SQL injections, using binded queries and Active records is safe, it will save you from SQL injections as the framework does all of the work of escaping vulnerable user input. There are few vulnerabilities with Active records, that are reported by users, they are however fixed quickly in suqsequent releases by the CodeIgnitor team ( EllisLabs)

Vapor - updating user property returns Precondition failed - id.exists

I found out why that issue is coming up. I was taking the user from the jwt like that - let user = try req.auth.require(AppUser.self) and after that I was modifying it and saving it to the database. What i did was to fetch the user from the database, modify it and after that save it to the database. It was saying that i can't update or save the user when I used the first option because the user from the jwt was like brand new and postgres was acting crazy because there was already a user with this id.

---

Related Topics