Schultzer

Schultzer

Hey there, I wrote this low-level library recently, its goal is simply to lower the barrier between Elixir and SQL, and it does that by providing a SQL sigil.

I’ll list a few highlights compared to raw SQL and Ecto.Query, although the goal with this library is not to replace either, but to provide fundamental safe building blocks to Ecto.SQL.Adapters.

  • Lower the barrier for DBAs to contribute to your codebase, without having to translate SQL to Ecto.Query.
  • Composable queries, no need for you to remember, when to start with select or from.
  • Interpolation-al queries, don’t fiddle with fragments and ?.
  • prevents SQL injection, by design.

You might have noticed that I’ve listed prevent SQL injection, and if you’re thinking: wait a minute, Ecto.Query is already by design preventing SQL injection.

You would be correct, However, Ecto.SQL also provides the query/2 and query!/2 functions that are vulnerable to SQL injection since they take a String and a list of params, the intention with this API, is to give you an escape hatch for executing parameterized queries.

This library is a result of a thread on the mailing list: https://groups.google.com/g/elixir-ecto/c/8MOkRFAdLZc

As this is an MVP, I’m looking for your feedback.

https://github.com/elixir-dbvisor/sql

Showing Posts 1 to 10

jstimps

jstimps

Very interesting work! It’s on my list of things to try out with EctoFDB.

My use case: FDB has no SQL so I’m thinking I could make a cli tool to query EctoFDB (similar to psql for Postgres) that uses the Ecto adapter and your SQL library.

binarytemple

binarytemple

Pure serendipity, I’ve been looking at a very similar challenge with nimble parsec yesterday.

Namely, splitting a file full of AWS Athena DDL/SQL statements (it only accepts one statement at a time) while interpolating some variables in the DDL statements.

I’m eager to read your implementation. There are some challenging edge cases such as things like user ‘O\’Malley’ and the ubiquitous’Little Bobby;drop tables’.

Schultzer

Schultzer OP

I would love to see that file, although I did start out with a version in nimble parsec, but I was pushing the limit of how I wanted to write the MVP and I didn’t wanna try to retro fit the grammar to make it composable.

https://github.com/dashbitco/nimble_parsec/issues/144
https://github.com/dashbitco/nimble_parsec/issues/140

So I ended up hand writing it. (this could change in the future though)

If I understand parser terminology right, then what I have written is a recursive decent parser / single pass as I read the string the AST is being built. And nimble parsec does not fit that well, but it would be great for spitting out tokens and then you could write some logic to analyze and build your AST.

The edge cases you have listed are those interpolated values or strings?

If the latter, then I’m not sure if my parser could handle those cases, as I only built it with the sigil in mind, not to migrate files of exotic SQL statements. Furthermore I have yet to implement DDL keywords in the parser and any keyword not tokenized by the parser would end up as ident tokens and passed through.

jstimps

jstimps

Did you try with Erlang’s yecc and leex?

Schultzer

Schultzer OP

Yes, I’ve used that in the past, and I might use it for this in the future. But generated parsers does not come without it flaws.

For this project I wanted to see if we could get all the benefits of Ecto.Query and Elixir with its composability but with less abstraction and more control.

Schultzer

Schultzer OP

I looked closely at this, and my parser does not support backticks yet. But it was pretty simple to add:

iex(18)> ~SQL[select `Little Bobby;drop tables`].query
[
  {:select, [line: 0, column: 6, end_line: 0, end_column: 32],
   [
     {:"``", [line: 0, column: 8, end_line: 0, end_column: 32],
      [
        {:ident, [line: 0, column: 8, end_line: 0, end_column: 32],
         [~c"Little Bobby;drop tables"]}
      ]}
   ]}
]

For what it’s worth, it looks like your examples got botched. I believe that only quotes and backticks are valid SQL syntax, but with that in mind, my parser would have trouble with O\Malley, but that also smells like invalid SQL, and should be: 'OMalley’:

iex(26)> ~SQL[select  'O`Malley'].query
[
  {:select, [line: 0, column: 6, end_line: 0, end_column: 17],
   [
     {:"''", [line: 0, column: 9, end_line: 0, end_column: 17],
      [
        {:ident, [line: 0, column: 9, end_line: 0, end_column: 17],
         [~c"O`Malley"]}
      ]}
   ]}
]
wojtekmach

wojtekmach

Hex Core Team

Awesome library, thank you for sharing this!

After a cursory look, my 2c:

Currently we have:

iex> ~SQL"SELECT 42"
select 42

I’d consider using ~SQL[...] or #SQL[...] in the inspect implementation:

iex> ~SQL"SELECT 42"
~SQL[select 42]

Currently we have:

x = 1 ; ~SQL"SELECT #{x}"

By convention we use interpolations with lowercase sigils but (to my dismay) we can’t have lowercase multi letter ~sql so another idea would be to have slightly different interpolation syntax, say:

x = 1 ; ~SQL"SELECT {x}"

Besides following convention I think doing this would actually be a security feature, never ever allow users to use string interpolations anywhere near SQL. Once users feel comfortable using this library that does the right thing they will inevitably (ok, I’m projecting, I definitely will) call sql = "select #{x}" ; Repo.query!(sql) and that can turn into a bug pretty fast. Using a distinct syntax like {...} prevents this. In hindsight, Repo.query! accepting sanitized strings (akin to, say, Phoenix.HTML.Safe) might have been a better default.

Schultzer

Schultzer OP

Thanks for the feedback, I’m currently working my way through the SQL grammar, once I can generate a parser from the BNF file, then we can make a decision based on our options, from what I can tell then braces are valid SQL syntax, but I couldn’t find any #.

Schultzer

Schultzer OP

As I’m polishing off the next version which currently is scheduled to be released in the coming weeks, and I got two exciting sneak peaks, we’re now more or less conformant with SQL 2016 with over 900 generated tests and SQL generation is 400-650x faster than Ecto.

belaustegui

belaustegui

This is amazing!

Where Next? Top

Trending in Announcing Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13183 112
New
achempion
Hi, I would like to tell about my initiative to further maintain and develop Waffle project which is the fork of Arc library. The progre...
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New
charlenopires
Hi everyone :waving_hand: I’ve been working on PhiaUI, an open-source UI component library for Phoenix LiveView, and I wanted to share i...
New

Other Trending Topics Top

OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
bartblast
If you haven’t tried Hologram yet, what’s stopped you? Here’s why I’m asking. Building a framework turns out to be two jobs, the framewo...
New
mat-hek
Popcorn, the library that runs Elixir code in the browser, is now stable :popcorn: This is possible because it now builds on a very stab...
New
AstonJ
With advancements in AI gathering pace and set to increasingly accelerate, perhaps it’s worth having an annual thread on the topic - feel...
New
sntran
In my third year of college, around 2010, I found Erlang. A few years later, I found Elixir. I liked the ideas at once: many small proces...
New
gtcode
I’ve been tinkering with Lynx (by José) via hold_ledger. I used AI to write the code, a separate specification, the tests, and the Lean p...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews