How to fix SQLITE_RANGE: 2nd parameter to sqlite3_bind out of range in SQLite

-- Count the ? or :param placeholders
SELECT * FROM users WHERE id = ?1 AND email = ?2
-- This statement has 2 parameters: index 1 and index 2

SELECT * FROM users WHERE id = ? AND email = ?
-- This also has 2 parameters: index 1 and index 2 (auto-numbered)

SELECT * FROM users WHERE id = :user_id AND email = :email
-- This also has 2 parameters (names don't matter for counting)

sqlite3_stmt *stmt;
const char *sql = "SELECT * FROM users WHERE id = ? AND email = ?";
int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

// Get the actual parameter count
int param_count = sqlite3_bind_parameter_count(stmt);
printf("SQL requires %d parameters\n", param_count);  // Output: 2

// Now you know to bind indices 1 through 2

const Database = require('better-sqlite3');
const db = new Database('test.db');

const sql = 'SELECT * FROM users WHERE id = ? AND email = ?';
const stmt = db.prepare(sql);

console.log('Parameter count:', stmt.parameters.length);  // Output: 2

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

sql = 'SELECT * FROM users WHERE id = ? AND email = ?'
# Count ? marks in SQL string
param_count = sql.count('?')
print(f"Parameters needed: {param_count}")  # Output: 2

// WRONG - Parameter indices start at 1, not 0
sqlite3_bind_int(stmt, 0, user_id);      // Index 0: ERROR (out of range)
sqlite3_bind_text(stmt, 1, email, -1, SQLITE_TRANSIENT);  // Index 1: Works

// CORRECT - Start parameter binding at index 1
sqlite3_bind_int(stmt, 1, user_id);      // Index 1: First parameter
sqlite3_bind_text(stmt, 2, email, -1, SQLITE_TRANSIENT);  // Index 2: Second parameter
sqlite3_bind_text(stmt, 3, name, -1, SQLITE_TRANSIENT);   // Index 3: Third parameter

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(':memory:');

const sql = 'INSERT INTO users (id, email, name) VALUES (?, ?, ?)';
db.run(sql, [
  1,              // Parameter 1: id
  '[email protected]', // Parameter 2: email
  'John Doe'       // Parameter 3: name
], (err) => {
  if (err) console.error('Bind error:', err);
});

const Database = require('better-sqlite3');
const db = new Database(':memory:');

const stmt = db.prepare('INSERT INTO users (id, email, name) VALUES (?, ?, ?)');
// Binding happens implicitly with array order
stmt.run(1, '[email protected]', 'John Doe');

// Or with explicit bind method
stmt.bind(1, '[email protected]', 'John Doe');
stmt.step();

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

sql = 'INSERT INTO users (id, email, name) VALUES (?, ?, ?)'
# Parameters are bound in order (effectively indices 1, 2, 3)
cursor.execute(sql, (1, '[email protected]', 'John Doe'))

# With named parameters (still start from beginning, not 0)
sql_named = 'INSERT INTO users (id, email, name) VALUES (:id, :email, :name)'
cursor.execute(sql_named, {'id': 1, 'email': '[email protected]', 'name': 'John Doe'})

require 'sqlite3'
db = SQLite3::Database.new ':memory:'

sql = 'INSERT INTO users (id, email, name) VALUES (?, ?, ?)'
# Array order determines parameter binding
db.execute(sql, [1, '[email protected]', 'John Doe'])

# Using bind with explicit indices
db.execute('SELECT * FROM users WHERE id = ? AND email = ?') do |stmt|
  stmt.bind_param(1, 1)      # Parameter 1: id
  stmt.bind_param(2, '[email protected]')  # Parameter 2: email
  stmt.step { |row| puts row.inspect }
end

// SQL has 2 parameters
const char *sql = "SELECT * FROM users WHERE id = ? AND email = ?";
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

// WRONG - Only binding 1 parameter when 2 are needed
sqlite3_bind_int(stmt, 1, user_id);  // Works
sqlite3_bind_text(stmt, 2, "...");   // Error if SQL didn't actually prepare the 2nd parameter

// What if SQL is malformed?
const char *bad_sql = "SELECT * FROM users WHERE id = ?";  // Only 1 parameter!
sqlite3_prepare_v2(db, bad_sql, -1, &stmt, NULL);
sqlite3_bind_int(stmt, 1, user_id);   // Works
sqlite3_bind_text(stmt, 2, "email");  // ERROR: Parameter 2 doesn't exist!

// First: Write SQL with exact parameter count needed
const char *sql = "INSERT INTO users (id, email, name) VALUES (?, ?, ?)";
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

// Get parameter count to validate
int expected_params = sqlite3_bind_parameter_count(stmt);
printf("Prepare expects %d parameters\n", expected_params);  // Output: 3

// Second: Bind exactly that many parameters
sqlite3_bind_int(stmt, 1, 123);           // Parameter 1
sqlite3_bind_text(stmt, 2, "[email protected]", -1, SQLITE_STATIC);  // Parameter 2
sqlite3_bind_text(stmt, 3, "John Smith", -1, SQLITE_STATIC);        // Parameter 3

// Third: Execute
sqlite3_step(stmt);
sqlite3_finalize(stmt);

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(':memory:');

// Wrong: Binding in a loop without matching SQL
const values = ['[email protected]', '[email protected]', '[email protected]'];
const sql = 'INSERT INTO users (email) VALUES (?)';  // Only 1 parameter!

const stmt = db.prepare(sql);
values.forEach((email, index) => {
  // WRONG - Trying to bind to indices 0, 1, 2, 3... but SQL only has 1 parameter
  stmt.bind(index, email);  // Error on iteration > 0
});

// Correct: Execute statement multiple times
values.forEach((email) => {
  db.run(sql, [email], (err) => {
    if (err) console.error('Insert error:', err);
  });
});

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Define your SQL
sql = 'SELECT * FROM users WHERE id = ? AND email = ?'

# Count parameters (simple approach)
param_count = sql.count('?')
print(f"SQL expects {param_count} parameters")

# Your data
params = (123, '[email protected]')

# Verify match
if len(params) != param_count:
    raise ValueError(f"Parameter mismatch: SQL expects {param_count}, got {len(params)}")

# Now safe to execute
cursor.execute(sql, params)

// WRONG - Multiple statements separated by semicolon
const char *sql = "DELETE FROM users; INSERT INTO users (id) VALUES (?)";
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

// Only the first statement (DELETE) is prepared
// It has 0 parameters
// Trying to bind to parameter 1 causes SQLITE_RANGE
sqlite3_bind_int(stmt, 1, 123);  // ERROR: Parameter 1 doesn't exist (DELETE has no params)

// CORRECT - Prepare and execute statements separately
sqlite3_stmt *stmt1, *stmt2;

// First statement
const char *delete_sql = "DELETE FROM users";
sqlite3_prepare_v2(db, delete_sql, -1, &stmt1, NULL);
sqlite3_step(stmt1);
sqlite3_finalize(stmt1);

// Second statement (has parameters)
const char *insert_sql = "INSERT INTO users (id) VALUES (?)";
sqlite3_prepare_v2(db, insert_sql, -1, &stmt2, NULL);
sqlite3_bind_int(stmt2, 1, 123);  // Now parameter 1 exists
sqlite3_step(stmt2);
sqlite3_finalize(stmt2);

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(':memory:');

// WRONG - Executing multiple statements in one call
const sql = `
  DELETE FROM users;
  INSERT INTO users (id, email) VALUES (?, ?);
`;
db.run(sql, [123, '[email protected]'], (err) => {
  // Error: Parameter binding fails
  if (err) console.error('Multi-statement error:', err);
});

// CORRECT - Execute statements separately
db.run('DELETE FROM users', (err) => {
  if (err) console.error('Delete error:', err);

  db.run('INSERT INTO users (id, email) VALUES (?, ?)',
         [123, '[email protected]'],
         (err) => {
    if (err) console.error('Insert error:', err);
  });
});

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# WRONG - Multiple statements at once
sql = "DELETE FROM users; INSERT INTO users (id, email) VALUES (?, ?)"
try:
    # Some drivers might split this, but it's unreliable
    cursor.execute(sql, (123, '[email protected]'))
except sqlite3.ProgrammingError as e:
    print(f"Error: {e}")  # Likely parameter binding error

# CORRECT - Separate statements
cursor.execute("DELETE FROM users")
cursor.execute("INSERT INTO users (id, email) VALUES (?, ?)", (123, '[email protected]'))
conn.commit()

import sqlite3

conn = sqlite3.connect(':memory:')

# executescript() is specifically designed for multiple statements
# But it doesn't support parameter binding
conn.executescript(`
  DELETE FROM users;
  INSERT INTO users (id, email) VALUES (123, '[email protected]');
`)

SELECT * FROM users WHERE id = ? AND email = ?
-- Parameter 1: id value
-- Parameter 2: email value

SELECT * FROM users WHERE id = ?1 AND email = ?2 AND name = ?3
-- Parameter 1: id value
-- Parameter 2: email value
-- Parameter 3: name value

SELECT * FROM users WHERE id = :id AND parent_id = :id
-- Parameter 1: :id (used twice but counted once)

SELECT * FROM users WHERE id = :id AND email = :email
-- Parameter 1: :id
-- Parameter 2: :email

-- WRONG - Mixing positional and numbered
SELECT * FROM users WHERE id = ? AND email = ?1 AND name = ?
-- This confuses the parser

-- WRONG - Positional with explicit indices
SELECT * FROM users WHERE id = ?1 AND email = ?
-- Unclear how indices are assigned

// Use either all positional or all numbered
// Positional style
const char *sql1 = "SELECT * FROM users WHERE id = ? AND email = ?";
sqlite3_prepare_v2(db, sql1, -1, &stmt, NULL);
sqlite3_bind_int(stmt, 1, 123);
sqlite3_bind_text(stmt, 2, "[email protected]", -1, SQLITE_STATIC);

// Numbered style
const char *sql2 = "SELECT * FROM users WHERE id = ?1 AND email = ?2";
sqlite3_prepare_v2(db, sql2, -1, &stmt, NULL);
sqlite3_bind_int(stmt, 1, 123);
sqlite3_bind_text(stmt, 2, "[email protected]", -1, SQLITE_STATIC);

// Named style
const char *sql3 = "SELECT * FROM users WHERE id = :id AND email = :email";
sqlite3_prepare_v2(db, sql3, -1, &stmt, NULL);
int id_idx = sqlite3_bind_parameter_index(stmt, ":id");
int email_idx = sqlite3_bind_parameter_index(stmt, ":email");
sqlite3_bind_int(stmt, id_idx, 123);
sqlite3_bind_text(stmt, email_idx, "[email protected]", -1, SQLITE_STATIC);

const Database = require('better-sqlite3');
const db = new Database(':memory:');

// Positional
const stmt1 = db.prepare('SELECT * FROM users WHERE id = ? AND email = ?');
stmt1.all(123, '[email protected]');

// Named
const stmt2 = db.prepare('SELECT * FROM users WHERE id = :id AND email = :email');
stmt2.all({ id: 123, email: '[email protected]' });

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Positional
cursor.execute('SELECT * FROM users WHERE id = ? AND email = ?', (123, '[email protected]'))

# Named
cursor.execute(
    'SELECT * FROM users WHERE id = :id AND email = :email',
    {'id': 123, 'email': '[email protected]'}
)

// Iterate through all parameters in a prepared statement
const char *sql = "SELECT * FROM users WHERE id = ?1 AND email = ?2 AND name = ?3";
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

int param_count = sqlite3_bind_parameter_count(stmt);
for (int i = 1; i <= param_count; i++) {
    const char *param_name = sqlite3_bind_parameter_name(stmt, i);
    if (param_name) {
        printf("Parameter %d name: %s\n", i, param_name);
    } else {
        printf("Parameter %d (unnamed)\n", i);
    }
}

#include <stdio.h>
#include <sqlite3.h>

void debug_prepared_statement(sqlite3_stmt *stmt) {
    // Get count of parameters
    int param_count = sqlite3_bind_parameter_count(stmt);
    printf("Statement has %d parameters\n", param_count);

    // List each parameter
    for (int i = 1; i <= param_count; i++) {
        const char *name = sqlite3_bind_parameter_name(stmt, i);
        if (name) {
            printf("  Parameter %d: %s\n", i, name);
        } else {
            printf("  Parameter %d: (unnamed/positional)\n", i);
        }
    }
}

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);

    sqlite3_stmt *stmt;
    const char *sql = "SELECT * FROM users WHERE id = ?1 AND email = ?2";
    sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

    // Debug before binding
    debug_prepared_statement(stmt);

    // Now safe to bind
    sqlite3_bind_int(stmt, 1, 123);
    sqlite3_bind_text(stmt, 2, "[email protected]", -1, SQLITE_STATIC);

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(db);
}

// Output:
// Statement has 2 parameters
//   Parameter 1: ?1
//   Parameter 2: ?2

import sqlite3

def inspect_prepared_statement(sql):
    """Analyze SQL parameters without executing"""
    # Count question marks and named parameters
    positional_count = sql.count('?')

    # Count unique named parameters
    import re
    named_params = re.findall(r'[:@$]\w+', sql)
    unique_named = len(set(named_params))

    print(f"SQL: {sql}")
    print(f"Positional parameters (?): {positional_count}")
    print(f"Named parameters: {unique_named}")
    if named_params:
        print(f"  Names: {set(named_params)}")

    total = max(positional_count, unique_named)
    print(f"Total bind calls needed: {total}")
    return total

# Usage
inspect_prepared_statement("SELECT * FROM users WHERE id = ? AND email = ?")
# Output:
# SQL: SELECT * FROM users WHERE id = ? AND email = ?
# Positional parameters (?): 2
# Named parameters: 0
# Total bind calls needed: 2

inspect_prepared_statement("SELECT * FROM users WHERE id = :id AND parent_id = :id")
# Output:
# SQL: SELECT * FROM users WHERE id = :id AND parent_id = :id
# Positional parameters (?): 0
# Named parameters: 1
#   Names: {':id'}
# Total bind calls needed: 1

const Database = require('better-sqlite3');
const db = new Database(':memory:');

function inspectStatement(sql) {
    const stmt = db.prepare(sql);
    console.log('SQL:', sql);
    console.log('Parameters:', stmt.parameters);
    console.log('Parameter count:', stmt.parameters.length);
    return stmt.parameters.length;
}

// Usage
inspectStatement('SELECT * FROM users WHERE id = ? AND email = ?');
// Output:
// SQL: SELECT * FROM users WHERE id = ? AND email = ?
// Parameters: [null, null]
// Parameter count: 2

inspectStatement('SELECT * FROM users WHERE id = :id');
// Output:
// SQL: SELECT * FROM users WHERE id = :id
// Parameters: ['id']
// Parameter count: 1

const Database = require('better-sqlite3');
const db = new Database(':memory:');

function safeExecute(sql, params) {
    const stmt = db.prepare(sql);

    // Validate parameter count
    if (stmt.parameters.length !== params.length) {
        throw new Error(
            `Parameter mismatch: SQL expects ${stmt.parameters.length}, \
             got ${params.length}. Expected: ${stmt.parameters.join(', ')}`
        );
    }

    return stmt.all(...params);
}

try {
    // This will throw before executing
    safeExecute('SELECT * FROM users WHERE id = ?', [123, 456]);
} catch (err) {
    console.error(err.message);
    // Output: Parameter mismatch: SQL expects 1, got 2
}