smart-contracts/Entity.txt : Database queries being formed with concatenation of strings/stringified values

[sveeke]

In smart-contract module, Entity.txt file, query is constructed using string concatenation and uses parameters directly, which may lead to query injection issues.

The attack vector is a peer node in the blockchain community - which can send malicious parameters causing SQL injection.

1. The WHERE clause uses $1 by concatenation Line 12,13: //Check if the institution exists and is allowed to create/unwithdraw entities. const institution = await query("SELECT", "institutions", "WHERE institution = $1;", [from]);

2. $2 and $3 are directly used by concatenation Line 27: "ON CONFLICT ON CONSTRAINT entities_pkey DO UPDATE SET allowed = true, name = $3 WHERE entities.institution = $2;", [payload.receiver, from, payload.name]);

3. $1 and $2 are used by concatenation Line 34: const changed = await query("UPDATE", "entities", "SET allowed = false WHERE entity = $1 AND institution = $2;"

[wdenbakker]

We have considered how to avoid SQL injections, especially because other parties may run their own instance of Validana and write their own contracts, possibly not taking SQL injections into account.

The first step is to support parameterized queries. This is done by pointing which parameter goes where (with $1 for the first parameter, $2 for the second, etc), followed by the list of parameters. I assume the confusion comes from the fact that other languages use similar syntax in string format functions, however this is not the case here. We make use of parameterized queries in these cases, thus avoiding SQL injections.

The next step is to make sure those who write smart contracts actually make use of parameterized queries. We do this be requiring the list of parameters to always be present, even if it is an empty list. This to serve as a constant reminder not to directly concatenate the parameters into the string. Without the list being present Validana will refuse to execute the query.

Lastly Validana refuses to run multiple queries with a single function call, or queries using comments. This as a last effort to avoid SQL injections if a developer failed to write proper queries.