SQL Database Access

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

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

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)

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.

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"))

# 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.

When the selected driver exposes prepared(...) or prepareStatement(...), use placeholders for all user-provided values. Parameter indexes are 1-based. This is the normal path for SQL Server and PostgreSQL connections on the current branch.

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"))

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.

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.

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

• 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 = ...:.