I have updated my SQL builder to do the check, which works fine with the
current code in db_postgres.nim.
The `dbValOrNull()` also removes my current checks for `if item[2] == "": nil
else: item[2]`.
Thank you all for the help and ideas! _(mikra I 'm still interested regarding
the bypass)_
type
ArgObj* = object
val: string
isNull: bool
ArgsContainer = object
query: seq[ArgObj]
args: seq[string]
var dbNullVal*: ArgObj
dbNullVal.isNull = true
proc sqlUpdate*(table: string, data: varargs[string], where:
varargs[string], args: ArgsContainer.query): SqlQuery =
## SQL builder for UPDATE queries
var fields = "UPDATE " & table & " SET "
for i, d in data:
if i > 0:
fields.add(", ")
if args[i].isNull:
fields.add(d & " = NULL")
else:
fields.add(d & " = ?")
var wes = " WHERE "
for i, d in where:
if i > 0:
wes.add(" AND ")
wes.add(d & " = ?")
result = sql(fields & wes)
proc argType*(v: ArgObj): ArgObj =
if v.isNull:
return dbNullVal
else:
return v
proc argType*(v: string | int): ArgObj =
var arg: ArgObj
arg.isNull = false
arg.val = $v
return arg
proc dbValOrNull*(v: string | int): ArgObj =
## Return NULL obj if len() == 0, else return value obj
if len($v) == 0:
return dbNullVal
var arg: ArgObj
arg.val = $v
arg.isNull = false
return arg
template genArgs*[T](arguments: varargs[T, argType]): ArgsContainer =
## Create argument container for query and passed args
var result: ArgsContainer
for arg in arguments:
let argObject = argType(arg)
if argObject.isNull:
result.query.add(argObject)
else:
result.query.add(argObject)
result.args.add(argObject.val)
result
# __ Test
let a = genArgs("[email protected]", 20, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, int
# ==> UPDATE myTable SET email = ?, age = ? WHERE name = ?
a = genArgs("", 20, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> NULL, int
# ==> UPDATE myTable SET email = NULL, age = ? WHERE name = ?
a = genArgs("[email protected]", "", "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, ERROR
# ==> UPDATE myTable SET email = ?, age = ? WHERE name = ?
# ==> To insert a NULL into a int-field, it is required to use
dbValOrNull() or dbNullVal,
# it is only possible to pass and empty string.
a = genArgs("[email protected]", dbNullVal, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, NULL
# ==> UPDATE myTable SET email = ?, age = NULL WHERE name = ?
let cc = ""
a = genArgs(dbValOrNull(cc), dbValOrNull(cc), "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> NULL, NULL
# ==> UPDATE myTable SET email = NULL, age = NULL WHERE name = ?
Run
