pedromvieira
Best practices to avoid Blind SQL Injection
Recently we passed a security audit for a new customer that used some security analysis tools and they pointed out some Blind SQL Injection issues (at least in theory).
What do you use to mitigate those risks in a Phoenix application?
All data goes through an API that receives params like this:
def kpi_data(conn, params) do
subdomain =
conn.assigns.subdomain
target =
params["target"]
|> String.to_atom()
items =
apply(KPI, target, [subdomain, params])
API.render_items(conn, items)
end
We do evaluate and discard any extra params and try to limit data in some parameters, but others are more open in nature or at least can receive a lot of entries (like items, or domains).
Our controller and functions clean it and load as WHERE clause in a SELECT statement.
If any params is utterly wrong, it throws an error and send a HTTP STATUS 500 error (And we follow “let it crash…”)
PS: We used sobelow and it dosen’t show any errors on those functions.
What are the best practices to avoid those risks?
Is there any other common functions / apps do you recommend?
Most Liked
benwilson512
Perhaps the “blind” here has meaning I haven’t heard before but SQL injection is not possible with the Ecto querying API. Ecto always uses parameterized queries, so values never end up in the SQL string at all.
You do have an issue with |> String.to_atom() since this lets a hostile API user use arbitrary amounts of memory, so that’s not great, but I’m not clear on how there’s an SQL issue.
EDIT: After reading What Are Blind SQL Injections | Acunetix it doesn’t look like you have to worry about this at all. These kinds of attacks are only possible if you interpolate user values into the SQL string, which Ecto never does.
NobbZ
Atoms are Not GC’d, and therefore the BEAM puts a limit on how many are allowed to exist. This limit is 2**20 by default.
Therefore using String.to_atom/1 at runtime is considered a flaw.
Instead String.to_existing_atom/1 is preferred or even better an explicit conversion function built from a whitelist.
benwilson512
As a minor note the execute function you have there seems the equivalent of the built in query! function. IE you can just do Repo.query!(sql, parameters).
What you have is either entirely fine or super dangerous depending on where the interpolation is coming from. If you make sure to NEVER interpolate user data then you’re safe. If you interpolate any user data you’re vulnerable to attack.
This is why the Ecto query DSL is nice, it can make sure at compile time that it’s impossible to interpolate user values.
Popular in Questions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"