Most performant lightweight library for working with PostgreSQL?

help
#1

Tokio_postgres, postgres and Diesel seem to be the most popular, but I need a simple one that will execute a bunch of SQL queries for a PostgreSQL database the fastest. Diesel as a query builder seems to be too complicated as I already have the necessary queries written down, I just need something to execute them.

With psycopg2 in Python it would be like:

con = connect()
cur = con.cursor()
    try:
        cur.execute(sql_query)
    except Exception:
        cur.rollback()
    else:
        con.commit()

I would appreciate a lightweight lib to handle it like that, but the performance is also very important.

Additionally, what lighweight library would be good for string sanitization to prevent SQL injections?

#2

These use the same underlaying implementation and only differs in that tokio_postgres exposes an async interface. I’ve never had any performance problems with either.

Additionally, what lighweight library would be good for string sanitization to prevent SQL injections?

I researched this many years back and concluded that there is basically no real good and reliable way to do this. I would suggest parameterized queries instead.

#3

Being able to use parameterized queries would be great, but I need to be able to change table name in SQL queries during runtime. I can't use parameterized queries for such a use case as far as I know.

#4

You don’t need to sanitize a dynamically built query string unless you plan on letting the user provide parts of the query itself. You can build up the query dynamically and still pass inn the data as parameters.

If you plan on letting the user provide the database name and need to sanitize that input I‘m not sure if there is a foolproof and secure way of handling that. Maybe you could use a lookup table instead where you map the user provided table name to a known valid table name you create programmatically and actually use in your queries.

1 Like
#5

The parameters should be supplied exclusively by an automated system and the chance of an sql injection should be non-existant, it's just me wanting to have it 100% secure and being pedantic, but I'll use formatted strings if there will be no other option to have a solid sanitize string function.

1 Like
#6

Being pedantic is good thing in this scenario. There might be something you can do to add at least some level of added security by validating the data from the automated system but I’m not aware of any library for that.

If this is a serious concern I would probably still consider the mapping table route and cache the maps from automated-system-table-name -> known-valid-sql-table-name for performance.

Maybe someone else chimes in with a good suggestion.

1 Like
#7

Unlike in the case of general string input, where parameterized queries are by far the best solution, identifiers for table names in PostgreSQL are quite constrained. From section 4.1.1 of the manual:

SQL identifiers and key words must begin with a letter ( a - z , but also letters with diacritical marks and non-Latin letters) or an underscore ( _ ). Subsequent characters in an identifier or key word can be letters, underscores, digits ( 0 - 9 ), or dollar signs ( $ ).

so you could validate that the table name is valid before formatting the string, possibly ensuring that the name isn’t an SQL reserved word either.

If there are no constraints on the table name then I might use the PostgreSQL Unicode quoted identifier syntax, and just quote every single character in the name. That might make debugging interesting, though.

#8

I just want to point out that diesel provides also a sql_query function beside the query builder. This function just accepts any string and executes it as query on the given database connection. It should be noted that the query string is passed without modification the the database system, so the user is responsible for providing a correct query there. As we already stated at multiple locations: This function is a important part of diesel's public API. It's fine to just sql_query and skip the query builder.