So the SQL drivers wrongly escape underscore in strings if you use the ? placeholder. But if you build the string yourself, the darn thing will error on a question mark since it wants to insert a place holder.
Is there any way to turn off the SQL ? placeholder thing and just build strings manually?
Never had any issue with SQL raw strings; maybe if you post your code we can help you debug it ?
Otherwise, allow me to introduce you to https://github.com/moigagoo/norm ?
Araq, To reproduce:
sql "INSERT INTO my_table (my_col) values (?)", "my_string"
In the database this becomes: "my_string"
I saw this in sqlite and mysql.
To work around this I started using: sql ""INSERT INTO my_table (my_col) values ('" & myvar & "')"
Now it doesn't escape underscores, but of course if the string in myvar contains a question mark I get an error...
Is there any way to turn off the automatic question mark replacement?
sql "INSERT INTO my_table (my_col) values (?)", "my_string"
Also it looks like norm uses the same underlying sql library so I don't think it would fix the problem.
db.exec(sql"""CREATE TABLE IF NOT EXISTS my_table (my_col text)"""
db.exec(sql"INSERT INTO my_table (my_col) values (?)", "my_string")
let myvar = "my?_string"
db.exec(sql"INSERT INTO my_table (my_col) values (?)", myvar)
echo db.getAllRows(sql"""SELECT * FROM my_table""")Output: @[@["my_string"], @["my?_string"]]sqlite> SELECT * FROM my_table;
my_string
my?_stringI am on Mac OS 11.4, this happens for me both in sqlite and mysql 5.7
When I run: db.exec(sql"INSERT INTO my_table (my_col) values (?)", "my_string")
I will get "my_string" in the library
The other ? thing, it is not a Nim bug, but it is the reason why the obvious work around doesn't work. (Hard to explain better than I did above, but maybe I should just have left that out since it just seems to confuse things)
you're inserting "my_string", which it's doing properly. I don't see any escaping of underscores either. if my_string is a variable, don't quote it when you pass it to, e.g. db.exec(query,my_string)
here dbFormat is lifted from db_sqlite
import db_sqlite
proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string =
result = ""
var a = 0
for c in items(string(formatstr)):
if c == '?':
add(result, dbQuote(args[a]))
inc(a)
else:
add(result, c)
let my_string = "hello"
let qry = sql"INSERT INTO my_table (my_col) values (?)"
var x = dbFormat(qry, "my_string")
assert x.string == "INSERT INTO my_table (my_col) values ('my_string')"
x = dbFormat(qry, my_string)
assert x.string == "INSERT INTO my_table (my_col) values ('hello')"
$ sqlite3 test.db SQLite version 3.34.1 Enter ".help" for usage hints. sqlite> SELECT * FROM my_table;
As I already said, this is a workaround:
sql "INSERT INTO my_table (my_col) values ('my_string')", "?" # ? will be replaced with ?
But if you create SQL via string concats and also do not know if the string contains a question mark, you're doing it completely wrong and have no protection against SQL injection attacks.
Sure, ad hominem is helpful.
Have a nice day
What I request from you is to insert from a minimal nim script and then check that the values in the DB were inserted escaped.
Don't do anything fancy with the insertion, four lines are enough:
import db_sqlite
let db = open("test.db", "", "", "")
db.exec(sql"CREATE TABLE IF NOT EXISTS my_table (my_col text)"
db.exec(sql"INSERT INTO my_table (my_col) values (?)", "my_string")Now go check what is in the DB with the sqlite command line. My experience debugging code without code posted is that 9 out of 10 times the problem is in the ghost code.