English
English
mysqli_stmt::prepare is a method in the mysqli extension used to prepare SQL statements. Compared to directly using SQL query strings, prepared statements reduce the risk of SQL injection. This is because user input is not directly concatenated into the query string but is instead passed as bound parameters.
<span><span><span class="hljs-variable">$conn</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-variable">$host</span></span><span>, </span><span><span class="hljs-variable">$user</span></span><span>, </span><span><span class="hljs-variable">$password</span></span><span>, </span><span><span class="hljs-variable">$database</span></span>);
<p></span>if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}</p>
<p>$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");<br>
</span>In the code above, the SQL query contains two question marks (?), which serve as placeholders for the parameters to be bound later.
In a prepared statement, the bind_param() method is used to bind user input values to the placeholders. The bound parameter values are not directly inserted into the SQL query but passed through the prepared statement. The bind_param() method requires two arguments: a type definition string and the variables to bind.
Common type specifiers include:
i: integer
d: double (floating-point)
s: string
b: BLOB (binary data)
<span><span><span class="hljs-variable">$username</span></span><span> = </span><span><span class="hljs-string">'john_doe'</span></span>;
</span><span><span class="hljs-variable">$password</span></span><span> = </span><span><span class="hljs-string">'password123'</span></span>;
<p></span>$stmt-></span>bind_param('ss', </span>$username, </span>$password);<br>
</span></span>In this example, 'ss' indicates that both parameters are strings. The $username and $password variables are securely bound to the SQL query placeholders.
After binding parameters, call the execute() method to run the prepared statement. If the parameter types are correct, the statement will execute successfully.
<span><span><span class="hljs-variable">$stmt</span></span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span>();
</span></span>The execute() method returns a boolean indicating whether the statement was executed successfully. For retrieving results, use the get_result() method (for SELECT queries).
For SELECT queries, after executing the prepared statement, you usually need to fetch the results. The get_result() method returns a mysqli_result object from which data can be retrieved.
<span><span><span class="hljs-variable">$result</span></span> = </span><span><span class="hljs-variable">$stmt</span></span>-></span><span><span class="hljs-title function_ invoke__">get_result</span></span>();
<p></span>while (</span>$row = </span>$result-></span>fetch_assoc()) {<br>
</span>echo </span>$row[</span>'username'] . </span>' - ' . </span>$row[</span>'email'] . </span>'<br>';<br>
}<br>
</span></span>In this example, the query results are fetched row by row, and the username and email are displayed.
Although prepared statements prevent SQL injection, various errors may still occur during development. For effective debugging, you can use the error method to capture error information.
<span><span><span class="hljs-keyword">if</span></span> (</span><span><span class="hljs-variable">$stmt</span></span> === </span><span><span class="hljs-literal">false</span></span>) {
</span><span><span class="hljs-keyword">die</span></span>(</span><span><span class="hljs-string">'Error preparing statement: '</span></span> . </span><span><span class="hljs-variable">$conn</span></span>->error);
}
<p></span>if (!</span>$stmt-></span>execute()) {<br>
</span>die(</span>'Execute error: ' . </span>$stmt->error);<br>
}<br>
</span></span>Here, conn->error and stmt->error are used to capture database connection and statement execution errors, which helps identify and fix problems quickly.
After using a prepared statement, it’s important to call the close() method to close both the statement and the database connection to free up resources.
<span><span><span class="hljs-variable">$stmt</span></span>-></span><span><span class="hljs-title function_ invoke__">close</span></span>();
</span><span><span class="hljs-variable">$conn</span></span>-></span><span><span class="hljs-title function_ invoke__">close</span></span>();
</span></span>This is not only a good programming practice but also an effective way to avoid resource leaks.
Related Tags:
mysqli_stmt
