Comments (11)
Prepared queries isn't just for escaping, they are actually handled differently in the database, making it so you can make queries more efficient by just having it compile once. This would be one feature that would be nice to get at least basic low-level support for.
from pgmoon.
correct, there's a manual escape interface
lapis supports that syntax: http://leafo.net/lapis/reference/database.html#query-interface
from pgmoon.
correct, prepared statements are a different feature than parameterized queries. You can use prepared statements by providing the appropriate SQL to the query method: https://www.postgresql.org/docs/9.6/static/sql-prepare.html
from pgmoon.
I was much more concerned about the security implications than the performance. Typically query planning takes far less time than the actual query for any sizable dataset. Prepared queries don't protect against SQL injection attacks. This is huge.
Everyone makes mistakes. Everyone gets tired, pressured, or distracted. Manual escaping relies on faulty humans. It is far too easy to forget (or have a new team member not yet understand the implications) and—this is the important part—still have the code work when doing any simple checks for functionality.
Whether the implementation uses prepared queries or not is just that: an implementation detail. It's usually a minor performance tweak. Parameterized queries on the other hand are a very different animal. They are not a database feature; they are a client library feature. For the time being, I've simply taken a slightly modified function from lapis to escape my params automatically. It's 2016. You don't just concatenate strings using user-supplied data. You just don't. Because humans make mistakes, and some mistakes are just too subtle.
from pgmoon.
As an aside, I've found "?" as a placeholder to be problematic. It's used for geometric and JSON comparisons—two features I'm personally rather fond of. It looks like every punctuation character is taken. It appears the only safe bet is the comment prefix: "--"
db:query("INSERT INTO cats (age, name, alive) VALUES (--, --, --)", 7, "Spot", true)
This should be safe since it's unlikely the client needs to send comments and they are categorically forbidden from use in raw queries without substantial breakage.
from pgmoon.
For my current application I do $1,$2,$3 which is pretty easy to parse and replace, and don't think they will be in typical SQL usage. The bonus is you can refer to them multiple times:
INSERT INTO users
(username, password)
SELECT $1, $2
WHERE
NOT EXISTS (
SELECT username FROM users WHERE username = $1
);
from pgmoon.
encode_array, encode_json, and encode_hstore all return a string value, losing your type context and potentially triggering a second literal escape. For things like arrays, JSON, and hstore, perhaps sending a closure would be more appropriate? Using @WetDesertRock's syntax…
db:query("INSERT INTO cats (age, name, alive, profile, kittens) VALUES ($1, $2, $3, $4, $5)",
7, "Spot", true, as_json(profile_table), as_array(kitten_table))
Where as_json/as_array/as_hstore return a closure
function as_json(obj)
return function() return encode_json(obj) end
end
so that the replacement logic can just check for
if type(param) == "function" then
return param()
else
return encode_literal(param)
end
from pgmoon.
@WetDesertRock, I hope your example was off the top of your head, and you're not storing passwords as plain text in your database…
from pgmoon.
@ttfkam the closure idea makes sense. I'm not entirely sure how gsub works but I'm not sure that would solve any issues. I'll encounter that when I get there.
Password is bcrypt'ed before being passed into the database, and when this project reaches any sort of real status I'll look into encrypting the column in the database. No worries, I'm not Yahoo.
from pgmoon.
@leafo Any reason to do not implement parameterized queries support?
I used https://github.com/SnarkyClark/luapgsql before, it uses nice syntax:
con:exec("select * from test where val1 >= $1 and val2 is null", {0})
con:exec("select * from test1 where f1=$1 and date1 between $2 and $2 + '1 hour'::interval", {1, '2020-01-01 18:00:00'})
For now I try to use openresty + pgmoon, but I lack parameters support.
from pgmoon.
I'm closing old issues. Query interpolation syntax is opinionated so it won't be added to pgmoon for now. Escape functions for literals and values will always be provided.
from pgmoon.
Related Issues (20)
- support ipv6 by using luasocket HOT 1
- What should I do if error happens when using pg:query ? HOT 1
- Implement extended query protocol HOT 1
- attempt to call method 'keepalive' (a nil value) HOT 1
- [PostgreSQL 14.1] Why is the connection wrong and the encryption method is scram-sha-256? HOT 2
- It works in neovim!
- unable to read data from frontend HOT 4
- bad request on repeated requests HOT 3
- multi-hosts HOT 1
- pgmoon support automatic master-slave switch HOT 1
- Add locking to query functions HOT 1
- authentication exchange unsuccessful in postgresql 15 scram-sha-256 in scram_sha_256_auth
- lua entry thread aborted: runtime error: attempt to yield across C-call boundary HOT 2
- multiple statements in one query HOT 2
- Support all array types
- postgres:query() does not return error as 2. parameter
- Socket pool getreusedtimes function HOT 1
- Pool violation after v1.13 HOT 2
- Connection pooling outside of OpenResty?
- which pgmoon version can connect to postgresql 15 ?
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pgmoon.