Advanced topics Databases SQL

# SQL Database Access

The `klyn.databases.sql` package provides a Klyn SQL facade for databases. Applications normally start with `DriverManager`, work through the common `Connection`, `Statement`, `PreparedStatement`, `CallableStatement`, and `ResultSet` interfaces, and let the URL choose the concrete driver.

Drivers and URLs

Importing `klyn.databases.sql` is enough for the generic facade. The current driver dispatcher recognizes MariaDB, Microsoft SQL Server, PostgreSQL, and ODBC URL families:

Database | Accepted URL prefixes | Driver package
--- | --- | ---
MariaDB | `mariadb://` | `klyn.databases.sql.mariadb`
SQL Server | `sqlserver://` | `klyn.databases.sql.sqlserver`
PostgreSQL | `postgresql://`, `postgres://` | `klyn.databases.sql.postgresql`
ODBC | `odbc:`, `odbc://` | `klyn.databases.sql.odbc`

Use the driver-specific packages only when you need implementation-specific types. Application code should prefer the common interfaces whenever possible.

ODBC Fallback

ODBC is currently the recommended solution when Klyn does not provide a native driver for a database. Configure the system ODBC driver and DSN first, then connect through `DriverManager` with the common SQL interfaces.

    import klyn.databases.sql

    # Uses the DSN named inventory from the local ODBC manager.
    try cnx = DriverManager.getConnection("odbc://inventory", "app_user", "secret"):
        try stmt = cnx.createStatement():
            try rows = stmt.executeQuery("select id, label from products order by id"):
                while rows.next():
                    print(rows.getInt("id"))
                    print(rows.getString("label"))

The `odbc://inventory` form maps to an ODBC connection string containing `DSN=inventory`. For deployments that already store a complete ODBC connection string, use the direct `odbc:` form instead:

    import klyn.databases.sql

    try cnx = DriverManager.connect("odbc:DSN=inventory;UID=app_user;PWD=secret"):
        print(cnx.metadata.productName)

Connecting to MariaDB

    import klyn.databases.sql

    url = "mariadb://localhost:3306/app"

    try cnx = DriverManager.getConnection(url, "app_user", "secret"):
        print(cnx.connected)
        print(cnx.metadata.productName)

`Connection`, `Statement`, `PreparedStatement`, `CallableStatement`, and `ResultSet` implement `AutoClosable`. Prefer `try resource = ...:` so sockets and cursors close even when SQL raises an exception.

What try-with-resources means

The `try resource = expression:` form is Klyn's try-with-resources syntax. It creates the resource, runs the block, and automatically calls `close()` at the end of the block, including when an exception is thrown. Nested SQL resources should be opened with nested `try` blocks so result sets close before statements, and statements close before the connection.

Static Queries

Plain statements are acceptable for static SQL whose text is fully controlled by the program: schema checks, migrations, administrative statements, and queries with no user-provided values.

    import klyn.databases.sql

    try cnx = DriverManager.getConnection("mariadb://localhost:3306/app", "app_user", "secret"):
        try stmt = cnx.createStatement():
            try rows = stmt.executeQuery("select id, email from users order by id"):
                while rows.next():
                    print(rows.getInt("id"))
                    print(rows.getString("email"))

SQL Injection Risk
Never concatenate user input into SQL

SQL injection happens when data is treated as SQL syntax. Any value coming from a request, command-line argument, file, network message, or UI field must be considered untrusted.

    # Unsafe: an attacker can change the meaning of the query.
    email = Application.arguments[0]
    sql = "select id, email from users where email = '" + email + "'"

A value such as `' or '1' = '1` can turn a filter into a condition that matches many rows. Escaping by hand is easy to get wrong, and it does not solve dynamic identifiers such as table names, column names, or sort directions.

Prepared Statements

Use `prepared(...)` or `prepareStatement(...)` and placeholders for all user-provided values. Parameter indexes are 1-based. Prepared statements are implemented by the current MariaDB, SQL Server, PostgreSQL, and ODBC connection families.

    import klyn.databases.sql

    email = Application.arguments[0]

    try cnx = DriverManager.getConnection("sqlserver://localhost:1433/app", "app_user", "secret"):
        try prepared = cnx.prepared("select id, email from users where email = ?"):
            prepared.setString(1, email)
            try rows = prepared.query():
                while rows.next():
                    print(rows.getInt("id"))
                    print(rows.getString("email"))

Do not fall back to concatenation

If a future or third-party driver raises `SQLFeatureNotSupportedException` for prepared statements, keep the query out of the user-input path. Use a stored procedure, redesign the operation around trusted static SQL, or add proper prepared-statement support to that driver.

MariaDB Stored Procedures

MariaDB applications can use `callable(...)` for stored procedures. Keep the call text static and bind values with setters such as `setInt` or `setString`.

    import klyn.databases.sql

    email = Application.arguments[0]

    try cnx = DriverManager.getConnection("mariadb://localhost:3306/app", "app_user", "secret"):
        try call = cnx.callable("{ call find_user_by_email(?, ?) }"):
            call.setString(1, email)
            call.out(2, Types.INTEGER)
            call.execute()

            userId = call.getInt(2)
            if call.wasNull():
                print("No user")
            else:
                print(userId)

The stored procedure should still validate authorization and data ownership. Parameter binding prevents values from becoming SQL syntax; it does not replace application-level access control.

Dynamic Identifiers

Placeholders represent values, not SQL identifiers. You cannot safely bind a table name, column name, sort expression, or SQL keyword with `?`. When a query needs a dynamic identifier, map external input to a small whitelist controlled by the program.

    def sortColumn(sortKey as String) as String:
        if sortKey == "email":
            return "email"
        if sortKey == "created":
            return "created_at"
        throw Exception("unsupported sort key")

    sortSql = sortColumn(Application.arguments[0])
    sql = "select id, email from users order by " + sortSql

The final concatenation is acceptable only because the appended SQL fragment comes from a fixed internal whitelist, not from the user.

Transactions

Disable `autoCommit` when multiple statements must succeed or fail together. Always roll back on the exceptional path before rethrowing the error.

    import klyn.databases.sql

    try cnx = DriverManager.getConnection("mariadb://localhost:3306/app", "app_user", "secret"):
        cnx.autoCommit = false
        try:
            try stmt = cnx.createStatement():
                stmt.executeUpdate("update accounts set locked = true where expired = true")
            cnx.commit()
        catch ex as SQLException:
            cnx.rollback()
            throw ex

Practical Rules

- Use `Statement` only for static SQL controlled entirely by the program.
- Use `PreparedStatement` for user-provided values when the driver supports it.
- Use `CallableStatement` for stored procedures and keep the call text static.
- Whitelist identifiers such as table names, column names, and sort directions.
- Never log passwords, connection URLs containing passwords, or raw user-provided SQL.
- Close every connection, statement, and result set with `try resource = ...:`.