In Python I use parameterised queries to prevent SQL injection. How does EK9 handle input sanitisation?

← Security and Sanitization · Ref: Q1015

EK9 builds input sanitisation into the parameter declaration with the 'sanitized' keyword.

Python relies on runtime discipline: cursor.execute with a %s placeholder and a parameter tuple is safe, while an f-string query built from user_input is UNSAFE - and nothing stops you writing the second.

In EK9 the function that RECEIVES the input declares it sanitized:

  buildQuery() as pure
    -> userInput as sanitized String
    <- rtn as String: `SELECT * FROM users WHERE name = ${userInput}`

This compiles - it is not a compile error. What 'sanitized' adds is a cleaning step at RUN TIME: the function or method that declares it cleans the parameter the moment it is entered, before any of its code runs, whatever route calls it. Ordinary input passes through unchanged; a detected threat (an injection, a script) is logged and arrives as an UNSET String, so anything built from it - here the query - is unset too and is never run.

Callers pass any String and write nothing extra - the declaration decides, so a caller cannot forget to clean the input. The compiler's part is to guarantee that the cleaning step is always there; it does not track the value afterwards.

Sanitized parameters work on functions, methods, constructors, and service endpoints.

Example

defines module qa.security.frompythonsqlsafety

  defines function

    //'sanitized': this function cleans userInput the moment it is entered,
    //at run time - a detected threat arrives as an UNSET String
    buildQuery() as pure
      -> userInput as sanitized String
      <- rtn as String: `SELECT * FROM users WHERE name = ${userInput}`

  defines program

    SqlSafetyDemo()
      stdout <- Stdout()

      //Ordinary input passes through unchanged
      query <- buildQuery("Alice")
      stdout.println(query)

      //An injection attempt is caught on entry: the parameter arrives unset,
      //so the query built from it is unset too, and is never run
      attack <- buildQuery("x' OR '1'='1")
      stdout.println(`Injection query is set: ${attack?}`)

Coming from another language?

Python developers: your parameterised-queries discipline becomes a parameter declaration in EK9. A 'sanitized' parameter is cleaned by the receiving code on every call, so injection input arrives unset instead of relying on every caller to remember.

Keywords: sql, python, sanitized, injection, compile time, security, migration