condition for quoting SQL NULL

[pedro-vicente]

In SQL I can INSERT a row in a database with a VARCHAR SQL type column named 'col1' with

std::string my_template = "INSERT INTO [my_table] (col1) VALUES ('my_value');"

To note that the SQL VARCHAR type requires to single quote

'my_value'

To insert a SQL NULL value, the single quotes are not specified

"INSERT INTO [my_table] (col1) VALUES (NULL);"

I have a template where the value is single quoted, and it allows to insert values like 'my_value'

std::string insert_template =
"INSERT INTO [my_table] (col1) VALUES ( '{{my_value}}' )";

nlohmann::json js;
to_json(js, my structure to convert to JSON);
std::string sql = inja::render(insert_template, js);

calling the template with a

std::string my_null = "NULL";

is incorrect because the string is single quoted resulting in a SQL insertion
of 'NULL' as a 4 character string and not an SQL NULL value

question

how can I make a condition to detect if the argument
'{{my_value}}' )

should be single quoted or not in the case the value is "NULL" ?

defining the template as (not single quoted)

std::string insert_template =
"INSERT INTO [my_table] (col1) VALUES ( {{my_value}} )";

would work for a NULL value but not a string

so, I would want something like (in pseudo INJA syntax)

std::string insert_template =
"INSERT INTO [my_table] (col1) VALUES ( 
{% if my_value == NULL %}
{{my_value}} 
{% else %}
'{{my_value}}'
{% endif %}
 )";

Is this possible to achieve somehow?

[pedro-vicente]

this construct in the template chooses the quoted version or not depending on the value being "NULL"

nlohmann::json json4;
  json4["name"] = "NULL";
  std::string template5 = "INSERT INTO [my_table] (col1) VALUES ( \
  {%if name == \"NULL\"%}\
  {{name}}\
  {%else%}\
  '{{name}}'\
  {%endif%}\
  )";

  std::string rendered5 = inja::render(template5, json4);
  std::cout << rendered5 << std::endl;

rendered string is

INSERT INTO [my_table] (col1) VALUES (     NULL    )

[kubo]

I would add the following function sql_quote to escape single quotation marks in string values. Otherwise it causes SQL injection.

As for SQL NULL:
json string "NULL" -> 'NULL'
json null -> null

  inja::Environment env;
  env.add_callback("sql_quote", 1, [](inja::Arguments& args) {
    using json = nlohmann::json;
    auto arg = args.at(0);
    switch (arg->type()) {
    case json::value_t::null:
      return json("null");
    case json::value_t::boolean:
    case json::value_t::number_integer:
    case json::value_t::number_unsigned:
    case json::value_t::number_float:
      break;
    case json::value_t::string: {
      const auto& s = arg->get_ref<const json::string_t&>();
      // uncomment the next line if string "NULL" must be SQL null.
      // if (s == "NULL") return json("null");
      json::string_t quoted;
      quoted.push_back('\'');
      for (char c : s) {
        if (c == '\'') {
          // escape a single quotation mark
          quoted.push_back('\'');
        }
        quoted.push_back(c);
      }
      quoted.push_back('\'');
      return json(quoted);
    }
    case json::value_t::object:
    case json::value_t::array:
    case json::value_t::binary:
    case json::value_t::discarded:
      throw std::runtime_error("cannot convert " + std::string(arg->type_name()) + " to sql literal");
    }
    return *arg;
  });

  json j;
  j["name"] = nullptr;
  std::string tmpl = "INSERT INTO [my_table] (col1) VALUES ({{ sql_quote(name) }})";
  auto rendered = env.render(tmpl, j);