I'm looking at using node.js/socket.io for a real-time user to user interface. I'm used to using PHP and it's got that great, really fool-proof prepared statements system. For example:

$dbh->prepare('SELECT * FROM table WHERE val=:val1 OR val=:val2');
$dbh->execute(array('val1'=>'stuff','val2'=>'more stuff'));

Now, node.js doesn't have this luxury, so I'm looking at doing something myself to simulate it. What exactly is actually HAPPENING here?

Thanks!

A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

1 First stage: prepare

$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

2 Execution

Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

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

3 Repeated execution

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

/* Prepared statement: repeated execution, only data transferred from client to server */
for ($id = 2; $id < 5; $id++) {
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}

/* explicit close recommended */
$stmt->close();

Prepared statements fundamentally separate the data and the command. SQL injection happens when the line between the two gets blurred by bad input. Separating them makes injection impossible.

To do this properly, you need a database server and client for that database with this capability. As you have discovered, many database clients do not actually use prepared statement functionality, but simply escape data for use in a query to emulate prepared statements. This can still be considered safe... those clients are heavily tested generally.

Note that you do not get the performance benefit of prepared statements when the client is simply emulating them.