Viewed   55 times

Prepared statments add a significant amount of code...yet I keep hearing mentions to use them...what value is added by going from 1 line of code to about 6? Is this simply to protect against sql injection?

Similar post here. on prepared statements here



Prepared statements offer excellent protection against SQL injection.

In addition to SQL injection protection, prepared statements offer reduced load on the database server when the same query is to executed multiple times, such as in an INSERT loop. The statement is only compiled once by the RDBMS rather than needing to be compiled each time as it would in a mysql_query() call.

Different APIs require varying amounts of code to execute a prepared statement. I find that PDO can be a little less verbose than MySQLi, if for example your situation permits the use of implicit parameter binding inside the execute() call. This only works, if all your params can be evaluated as strings though.

// PDO implicit binding example:
// Not many lines of code if the situation allows for it
$stmt = $pdo->prepare("SELECT * FROM tbl WHERE col1=? AND col2=? AND col3=?");
$stmt->execute(array($val1, $val2, $val3));
Tuesday, December 20, 2022

No, prepared queries (when used properly) will ensure data is properly escaped for safe querying. You are kind of using them properly, just need change one little thing. Because you are using the '?' placeholder, it is better to pass params through the execute method.


Just be careful if you're outputting that to your page, database sanitization does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well.

Monday, October 10, 2022

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.


$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
    $a = chr($i % 1);
    $b = chr($i % 2);

echo microtime(true) - $start;


Sunday, November 6, 2022

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See:

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Wednesday, August 24, 2022

That should contain all the information you need.

Otherwise, the standard mysql_* functions do not provide functionality for prepared statements.

Tuesday, August 30, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :