How to fix CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters in MySQL

#include <mysql.h>
#include <string.h>

MYSQL_STMT *stmt = mysql_stmt_init(mysql);
if (!stmt) {
    fprintf(stderr, "mysql_stmt_init() failed\n");
    return 1;
}

const char *query = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
    fprintf(stderr, "Prepare failed: %s\n", mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    return 1;
}

// Query has 3 placeholders - must bind exactly 3 parameters
MYSQL_BIND bind[3];  // Must match placeholder count!
memset(bind, 0, sizeof(bind));

// Bind parameter 1: name (string)
const char *name = "John Doe";
unsigned long name_length = strlen(name);
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (void *)name;
bind[0].buffer_length = name_length;
bind[0].length = &name_length;

// Bind parameter 2: email (string)
const char *email = "[email protected]";
unsigned long email_length = strlen(email);
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = (void *)email;
bind[1].buffer_length = email_length;
bind[1].length = &email_length;

// Bind parameter 3: age (integer)
int age = 30;
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = (void *)&age;

// NOW bind all parameters before execute
if (mysql_stmt_bind_param(stmt, bind)) {
    fprintf(stderr, "Bind failed: %s\n", mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    return 1;
}

// ONLY NOW execute - all parameters are bound
if (mysql_stmt_execute(stmt)) {
    fprintf(stderr, "Execute failed: %s\n", mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    return 1;
}

mysql_stmt_close(stmt);

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

// WRONG: 3 placeholders but only 2 parameters
const query1 = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
const values1 = ['John', '[email protected]'];  // Missing age!
// connection.query(query1, values1, callback);  // ERROR 2031!

// CORRECT: Match placeholder count
const values2 = ['John', '[email protected]', 30];  // All 3 parameters
connection.query(query1, values2, (error, results) => {
  if (error) throw error;
  console.log('Insert successful');
});

// For SELECT with WHERE clause
const query3 = 'SELECT * FROM users WHERE id = ? AND active = ?';
const values3 = [123];  // WRONG: missing active parameter

const values4 = [123, 1];  // CORRECT: both parameters provided
connection.query(query3, values4, (error, results) => {
  if (error) throw error;
  console.log(results);
});

// Debugging: Log query and parameters
console.log('Query:', query3);
console.log('Parameters:', values4);
const placeholders = (query3.match(/\?/g) || []).length;
const provided = values4.length;
if (placeholders !== provided) {
  console.error(`Mismatch: query has ${placeholders} placeholders but ${provided} parameters provided`);
}

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Prepare statement with 3 placeholders
$query = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($query);

if (!$stmt) {
    die("Prepare failed: " . $mysqli->error);
}

// WRONG: Only binding 2 parameters for a query with 3 placeholders
// $stmt->bind_param("ss", $name, $email);  // Missing age!
// $stmt->execute();  // ERROR 2031: missing age parameter

// CORRECT: Bind ALL parameters before execute
$name = "John Doe";
$email = "[email protected]";
$age = 30;

// bind_param signature: "types", var1, var2, var3, ...
// i = integer, d = double, s = string, b = blob
$stmt->bind_param("ssi", $name, $email, $age);

if (!$stmt->bind_param("ssi", $name, $email, $age)) {
    die("Bind failed: " . $stmt->error);
}

if (!$stmt->execute()) {
    die("Execute failed: " . $stmt->error);
}

echo "Insert successful";
$stmt->close();
$mysqli->close();
?>

// WRONG: Building parameters conditionally can miss placeholders
let query = 'SELECT * FROM users WHERE 1=1';
let params = [];

if (filter.name) {
  query += ' AND name = ?';
  params.push(filter.name);
}

if (filter.email) {
  query += ' AND email = ?';
  params.push(filter.email);
}

if (filter.age) {
  query += ' AND age = ?';
  // Forgot to push filter.age!
}

// query might have 3 placeholders but only 2 parameters
db.query(query, params, callback);  // May error if age filter is set

// CORRECT: Always push parameter if adding placeholder
let query2 = 'SELECT * FROM users WHERE 1=1';
let params2 = [];

if (filter.name) {
  query2 += ' AND name = ?';
  params2.push(filter.name);
}

if (filter.email) {
  query2 += ' AND email = ?';
  params2.push(filter.email);
}

if (filter.age) {
  query2 += ' AND age = ?';
  params2.push(filter.age);  // ALWAYS push when adding ?
}

db.query(query2, params2, callback);

// Even better: Use a reusable helper
function buildQuery(filters) {
  const conditions = [];
  const params = [];
  
  for (const [key, value] of Object.entries(filters)) {
    if (value !== undefined && value !== null) {
      conditions.push(`${key} = ?`);
      params.push(value);
    }
  }
  
  return {
    query: `SELECT * FROM users WHERE ${conditions.join(' AND ')}`,
    params: params
  };
}

const { query: finalQuery, params: finalParams } = buildQuery(filter);
db.query(finalQuery, finalParams, callback);

// JavaScript example
function executeQuery(sql, params) {
  // Validation
  const placeholderCount = (sql.match(/\?/g) || []).length;
  const paramCount = Array.isArray(params) ? params.length : 0;
  
  if (placeholderCount !== paramCount) {
    console.error('ERROR: Parameter count mismatch!');
    console.error(`SQL: ${sql}`);
    console.error(`Expected ${placeholderCount} parameters, got ${paramCount}`);
    console.error(`Parameters provided:`, params);
    throw new Error(`CR_PARAMS_NOT_BOUND: ${placeholderCount} placeholders but ${paramCount} parameters`);
  }
  
  console.log(`Executing: ${sql}`);
  console.log(`Parameters: ${JSON.stringify(params)}`);
  
  return db.query(sql, params);
}

// Usage
try {
  await executeQuery(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['John']  // Missing email - will throw with helpful message
  );
} catch (err) {
  console.error(err.message);
}

// WRONG: Building placeholders without matching parameters
const users = [
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' }
];

let query = 'INSERT INTO users (name, email) VALUES ';
let values = [];

for (let i = 0; i < users.length; i++) {
  if (i > 0) query += ', ';
  query += '(?, ?)';
  // Forgot to push the actual values!
  // values.push(users[i].name, users[i].email);
}

db.query(query, values, callback);  // Query has 4 placeholders, values is empty!

// CORRECT: Synchronize placeholders with parameter push
let query2 = 'INSERT INTO users (name, email) VALUES ';
let values2 = [];

for (let i = 0; i < users.length; i++) {
  if (i > 0) query2 += ', ';
  query2 += '(?, ?)';
  values2.push(users[i].name, users[i].email);  // Push immediately
}

db.query(query2, values2, callback);

// Best practice: Use a helper function
function buildInsertQuery(table, records) {
  if (records.length === 0) throw new Error('No records to insert');
  
  const columns = Object.keys(records[0]);
  const placeholders = columns.map(() => '?').join(', ');
  const rowPlaceholders = Array(records.length)
    .fill(null)
    .map(() => `(${placeholders})`)
    .join(', ');
  
  const query = `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${rowPlaceholders}`;
  const params = records.flatMap(record => columns.map(col => record[col]));
  
  return { query, params };
}

const { query: sql, params } = buildInsertQuery('users', users);
db.query(sql, params, callback);

// WRONG: Reusing statement without rebinding for new values
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, "INSERT INTO logs (message) VALUES (?)", 45);

MYSQL_BIND bind[1];
memset(bind, 0, sizeof(bind));

const char *msg1 = "First log";
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (void *)msg1;
bind[0].buffer_length = strlen(msg1);

mysql_stmt_bind_param(stmt, bind);
mysql_stmt_execute(stmt);

// Trying to execute again with different data
const char *msg2 = "Second log";
// WITHOUT rebinding, MySQL will still use msg1's data
mysql_stmt_execute(stmt);  // Uses msg1 again, not msg2!

// CORRECT: Rebind before each execute with different data
mysql_stmt_bind_param(stmt, bind);  // Rebind with msg1
mysql_stmt_execute(stmt);

bind[0].buffer = (void *)msg2;  // Change buffer pointer
bind[0].buffer_length = strlen(msg2);
mysql_stmt_bind_param(stmt, bind);  // Rebind with msg2
mysql_stmt_execute(stmt);  // Now executes with msg2

mysql_stmt_close(stmt);