Asked  9 Months ago    Answers:  5   Viewed   80 times

I'm currently using the mysqli php extension.

Traditionally I have used mysqli_real_escape_string to escape user input. However I am looking at changing over the code (hopefully in as few steps as possible) to use prepared statements.

I want to be clear on this - provided I use prepared statements to bind all of my variables, can I be confident that sql injection is impossible? (And dispense completely with mysqli_real_escape_string?)

Thanks

 Answers

11

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Wednesday, March 31, 2021
 
Keat
answered 9 Months ago
50

you forgetting the line to fetch the result. fetch() .

try that:

  $stmt->bind_result($first_name, $last_name);
  $stmt->fetch();  // ----- > you forget that line to fetch results.
  $stmt->close();
Wednesday, March 31, 2021
 
Anax
answered 9 Months ago
93

Prepared statements and transactions are unrelated techniques and technologies.

You may wish to issue the START TRANSACTION and COMMIT/ROLLBACK commands directly instead of using the dedicated methods. They are functionally equivalent.

For your loop, you'd issue the START TRANSACTION before your prepare, then your COMMIT after the loop exits. You probably should not try to open a transaction after a prepared statement has been started but before it's been executed.

For some reason, they didn't add a "start transaction" command in favor of turning off autocommit. It's one of those weird things about mysqli that makes me always recommend PDO instead. :) Opening a transaction implicitly turns off autocommit for the duration of the transaction.

Wednesday, March 31, 2021
 
ranhan
answered 9 Months ago
14

First you create the statement very much like a normal statement you have made

$stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text)
SELECT c.id, ?
FROM categories AS c WHERE c.id = ?;");

Get the statement bound to the parameter 's' stands for string data and i for integer

$stmt->bind_param('si', $joke_text,$category_id);   // bind to the parameters

/* execute prepared statement */

$stmt->execute();
Saturday, May 29, 2021
 
Saxophlutist
answered 7 Months ago
23

Your isolated and simplified example is technically safe.

However, there are still two problems with it:

  • the assumption: the very statement of question is made out of the assumption that mysqli_real_escape_string() is related to any security issues. Which is but a grave delusion. This is a string formatting function, that protects you from SQL injections only as a side effect. But such a protection is neither the goal nor the purpose of the function. And therefore it should never be used for the purpose.
  • the inherent separability of the code you posed. The protection consists of three parts:
    • setting the correct encoding
    • escaping special characters
    • wrapping the escaped value in quotes

It is not only the fact that some of these obligatory measures could be forgotten but again, the statement of question stresses only on a single part - escaping. It is only escaping which is always accented on, while two other measures get hardly mentioned at all. Just look at your question - you meant the code but asked about a function. So any literal answer to the question you asked will make a fatally wrong impression that mysqli_real_escape_string() is all right.

In short, the statement of question helps to promote the most dangerous of PHP related delusions: that this function protects from SQL injection.

Unlike this complex three-part equation, prepared statements constitute an inseparable measure. You cannot forget one part. You cannot misuse it. Try mysqli_real_escape_string() to protect an identifier, and it will silently go unnoticed, until the actual injection happen. Try a prepared statement for an identifier - and get an error.

Tuesday, October 19, 2021
 
1.21 gigawatts
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share