Prepared Statements, Parameters, Injection Defense, Type Mapping, and Unicode

Learning outcomes

ServiceHub’s search endpoint accepts a customer-supplied string, and an early implementation creates SQL with template interpolation. At the same time, billing values arrive as JavaScript numbers, emoji occasionally corrupts in old environments, and UUID/JSON fields are treated as interchangeable strings. The problem is broader than SQL injection: applications need an explicit representation contract between language values, connector encodings, MariaDB types, and collations.

1. Parameters separate SQL grammar from values

When application code concatenates a value into SQL text, the database parser cannot know which characters were intended as data and which were intended as syntax. A bound parameter lets the connector encode the value separately from the statement structure. That is the primary injection defense for values, but it is not a magic templating language: placeholders do not safely substitute arbitrary table names, sort directions, operators, or SQL fragments.

2. Build an explicit type lab

DROP DATABASE IF EXISTS servicehub19_l2;CREATE DATABASE servicehub19_l2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub19_l2;CREATE TABLE invoices (  invoice_id BIGINT PRIMARY KEY AUTO_INCREMENT,  external_key VARCHAR(64) NOT NULL UNIQUE,  customer_name VARCHAR(120) CHARACTER SET utf8mb4 NOT NULL,  amount DECIMAL(18,4) NOT NULL,  issued_at DATETIME(6) NOT NULL,  payload_json LONGTEXT CHARACTER SET utf8mb4 NULL,  receipt_hash VARBINARY(32) NULL,  CHECK (JSON_VALID(payload_json) OR payload_json IS NULL)) ENGINE=InnoDB;DROP USER IF EXISTS 'svc19_param'@'127.0.0.1';CREATE USER 'svc19_param'@'127.0.0.1' IDENTIFIED BY 'local-param-lab';GRANT SELECT, INSERT, UPDATE ON servicehub19_l2.* TO 'svc19_param'@'127.0.0.1';

DECIMAL(18,4) is intentionally chosen for money-like exact decimal values. JavaScript Number is binary floating point, so applications that require exact decimal semantics should use a decimal library/string representation compatible with the connector’s documented type handling rather than assuming every decimal can round-trip exactly through Number.

3. Wrong approach: build SQL with string interpolation

// Deliberately unsafe: do not use this pattern.const name = req.query.name;const sql = `SELECT invoice_id, customer_name             FROM invoices             WHERE customer_name = '${name}'`;const rows = await conn.query(sql);

The mistake is not a particular attack string; the mistake is allowing untrusted bytes to participate in SQL grammar construction. The repair is to keep the SQL text fixed and bind the value.

const rows = await conn.query(  `SELECT invoice_id, customer_name     FROM invoices    WHERE customer_name = ?`,  [req.query.name]);

4. Prepared statements, batches, and placeholder limits

const stmt = await conn.prepare(`  INSERT INTO invoices    (external_key, customer_name, amount, issued_at, payload_json, receipt_hash)  VALUES (?, ?, ?, ?, ?, ?)`);try {  await stmt.execute([    'inv-2026-0001',    'Nadia ☕',    '1250.3750',    new Date('2026-08-20T12:00:00Z'),    JSON.stringify({ channel: 'api', priority: 2 }),    Buffer.alloc(32, 7)  ]);} finally {  await stmt.close();}

Prepared statements can reduce repeated parse work and give explicit bind semantics, but measure before claiming a performance gain. A placeholder represents a value position. For dynamic ORDER BY, column names or table names, choose from a hard-coded allow-list and assemble only those trusted structural tokens.

5. Unicode and collation are four separate decisions

SELECT @@character_set_client, @@character_set_connection, @@character_set_results,       @@collation_connection;SHOW CREATE TABLE servicehub19_l2.invoices;SELECT invoice_id, customer_name, HEX(customer_name) AS utf8_bytes,       amount, issued_at, JSON_VALID(payload_json) AS json_validFROM servicehub19_l2.invoices;

utf8mb4 answers “can these code points be represented?” Collation answers a different question: how strings compare and sort. Do not switch collations casually to repair one query; it can change uniqueness, ordering and index semantics.

6. Type mapping and boundary checks

MariaDB has server types; the connector maps them into JavaScript representations. Large integer values may exceed JavaScript’s safe integer range. Exact decimals can lose precision if converted to floating point. DATETIME has no intrinsic time-zone offset; TIMESTAMP conversion depends on session time zone. JSON in MariaDB has semantics that differ from MySQL’s binary JSON implementation, so treat the MariaDB target version as authoritative.

7. Reproducible lab, verification, and cleanup

npm install mariadb# Set DB_PASSWORD=local-param-lab using your shell's environment syntax.

const mariadb = require('mariadb');const pool = mariadb.createPool({  host: '127.0.0.1', user: 'svc19_param', password: process.env.DB_PASSWORD,  database: 'servicehub19_l2', connectionLimit: 2, charset: 'utf8mb4'});const conn = await pool.getConnection();try {const values = {  key: 'inv-2026-emoji',  name: 'Mina 🌍',  amount: '9999999999.1250',  when: new Date('2026-08-20T18:30:00Z')};await conn.query(  `INSERT INTO invoices(external_key,customer_name,amount,issued_at)   VALUES (?,?,?,?)`,  [values.key, values.name, values.amount, values.when]);const [row] = await conn.query(  `SELECT external_key,customer_name,amount,issued_at,HEX(customer_name) AS bytes     FROM invoices WHERE external_key=?`, [values.key]);console.log(row);} finally {  conn.release();  await pool.end();}

DROP USER IF EXISTS 'svc19_param'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub19_l2;

Production judgment and bridge to Lesson 3

Make bound values the default, allow-list dynamic SQL structure, keep exact types exact, and test Unicode/collation behavior with real business strings. Once values are safe, multiple statements still need a correctness boundary. Lesson 3 turns those statements into transaction units, then deals with deadlocks, timeouts, retries and the most difficult case: a network break where the client cannot tell whether the server committed.

Mechanism deep dive: values, SQL structure, and type fidelity are different concerns

Parameter binding protects values; it does not turn arbitrary SQL structure into data. Table names, column names, sort direction, operators, and SQL keywords normally cannot be supplied through ordinary value placeholders. When an application needs dynamic structure, choose from an allow-listed set of identifiers or prebuilt query shapes, then bind only the data values. This separation is stronger than escaping because it keeps the SQL grammar under application control while the connector transmits values through its supported parameter mechanism.

Prepared execution also has a type contract. A MariaDB DECIMAL value can exceed the exact precision of a JavaScript floating-point number, so a connector may expose it as text or another precision-preserving representation. BIGINT has a similar boundary. Date/time values need an explicit policy for server time zone, connector conversion, and whether the application interprets a value as a wall-clock time or an instant. Binary data should remain binary rather than pass through a text encoding accidentally. JSON requires remembering MariaDB's representation and connector conversion rules instead of assuming another database's native-binary JSON behavior.

Unicode correctness is end to end: client encoding, connection character set/collation, column character set/collation, application string handling, and comparison rules all matter. A successful insert of ASCII data proves almost nothing about emoji, combining characters, supplementary-plane code points, or locale-sensitive ordering. A useful test corpus includes non-ASCII names, emoji, case/accent variants, and byte-for-byte round trips for binary fields. Verify both returned characters and comparison/order behavior.

For bulk work, distinguish “many parameter sets for one statement shape” from concatenating one enormous SQL string. A connector's batch API can reduce round trips while preserving parameterization, but transaction size, redo/binlog volume, lock duration, packet limits, and replica/Galera effects still need measurement. Batch size is therefore a workload variable, not a universal constant.

Verification pattern: prove the statement shape and the returned type contract

For critical paths, log or test the normalized statement shape separately from values. Assert that user-controlled data appears only in bound parameter positions and that any dynamic identifier comes from an allow-list. Then inspect returned metadata/types for representative DECIMAL, BIGINT, temporal, JSON/text, binary, NULL, and Unicode values. This catches security and correctness regressions when a connector major version changes defaults or type conversion behavior.

Authoritative references

Primary references are current MariaDB documentation or official connector source; verify the target server/connector version before relying on defaults or option behavior.

• MariaDB Connector/Node.js Promise API
• MariaDB prepared statements
• MariaDB data types
• MariaDB character sets and collations