Introducing Terpal-SQL. This Kotlin library is ins...

# feed

[Alexander Ioffe]

Introducing Terpal-SQL. This Kotlin library is inspired by Scala libraries such as Doobie and ZIO-JDBC. It allows SQL authoring using interpolated strings that are SQL-Injection-safe because they are never spliced into the parent string. (This is done by the Terpal compiler plugin).

Terpal-SQL supports Queries i.e. SELECT, actions i.e. INSERT, UPDATE, DELETE, and Batch Actions. Actions can also return a value or record if the underlying database supports it. It also supports transactions using coroutine scopes as well as query-result streaming.

// Uses Kotlinx Serialization, not Reflection!
@Serializable
data class Person(val id: Int, val firstName: String, val lastName: String)

// Run a Query, decode into a Data Class 
val person: List<Person> = Sql("SELECT * FROM Person WHERE id = $id").queryOf<Person>().runOn(ctx)

Terpal-SQL does not use JVM reflection to construct or encode objects. It uses the Kotlinx-Serialization library in order to produce encoders and decoders. This makes it ideal for future extensions for non-JVM targets such as Kotlin multiplatform (please let me know if you are interested in using Terpal with KMP!).

https://github.com/deusaquilus/terpal-sql

[CLOVIS]

How do you protect against injections? The README says it does, but doesn't explain how it works.

[Alexander Ioffe]

It uses the Terpal compiler plugin to extract the $dollar $sign $variables before they are spliced into the parent string and puts them into a separate data-structure. There’s a more detailed explanation here:
https://github.com/deusaquilus/terpal

[CLOVIS]

What if the user forgets to apply the compiler plugin? It would do a regular string interpolation there, no?

[CLOVIS]

This is a very powerful feature that allows libraries to create DSLs that are both safe and easy to use. Sadly Kotlin does not have it.

yet 👀 https://kotlinlang.slack.com/archives/CT0G9SD7Z/p1718216286102869

[CLOVIS]

I like this idea! Looking at the library, I was a bit worried injections would be easy.

Next step, do you think you could make this typesafe? After all, you now are processing the request before it is sent. Checking that parameters are of the expected type may be possible?

[Alexander Ioffe]

Answering your previous question, if the Terpal plugin is not included the code will not compile because io.exoquery.terpal.Interpolator is missing. Then if you really try to be clever and include terpal-runtime (I.e the module that contains Interpolator) in the dependencies without the terpal plugin, you’ll get the following error the second Sql is called:

[Alexander Ioffe]

It’ll sooner blow up the application than let the injection-splice happen.

[CLOVIS]

Perfect 💯

[CLOVIS]

Could a user accidentally call Interpolator.interpolate ?

[Alexander Ioffe]

You could intentionally call Interpolator.interpolate with bad strings I guess.

[Alexander Ioffe]

It wouldn't happen unintentionally though.

[CLOVIS]

interpolate takes a string + the list of parameters, and bakes the query, right?

I can't look at the code right now, so I don't know if you've done it, but just in case: it should be annotated with an PossibleInjectionApi opt-in or similar to make it really easy to audit

[Alexander Ioffe]

Good point! I'll look into that.

[CLOVIS]

https://kotlinlang.org/docs/opt-in-requirements.html

[CLOVIS]

This way you're sure it can't be accidental, and it's immediately obvious in review if someone tries to silence the warning

[CLOVIS]

As a library author it does force you to be exhaustive and mark all risky functions though, it can be a bit painful. But I think it's worth it

[Alexander Ioffe]

That's cool. I like defining API surface areas.

[Alexander Ioffe]

Also, in terms of type-safety the system is about as typesafe as I can make it without directly messing around with the frontend-IR (which I'd like to avoid). There are only two things you're allowed to splice into a Sql("...") string clause. A sql.Param and a sql.Statement. If you try to splice a different datatype e.g. Int, Long, Float, Double, etc... it will attempt to wrap it in a sql.Param object at compile-time. If it can't do that it will fail to compile with the following message:

[Alexander Ioffe]

If you want to wrap a custom-datatype in a Param object you need to either define a kotlinx-serialization Serializer or mark it as contextual (which tells Terpal-SQL that it needs to find a JDBC encoder for it).

[CLOVIS]

I like this way of doing it

[Alexander Ioffe]

You can have a look at the Customer Parameters doc for more info.