When should prepared statements be used?

Question

I originally used mysql_connect and mysql_query to do things. Then I learned about SQL injection, so I'm trying to learn how to use prepared statements. I understand how the preparation and execution functions of the PDO class are useful in preventing SQL injection.

Are prepared statements needed only when user input is stored in the database? Would it be nice to still use mysql_num_rows , since I really don't risk being hacked with this function? Or is it safer to use prepared statements for this? Should I use prepared statements for anything related to using MySQL? Why?

Answer 1

TL / DG

Always. 100% of the time, use it. Always; and even if you don’t need to use it. USE STILL.

---

mysql_* functions are deprecated. ( ^{Pay attention to the big red square?} )

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See Also MySQL: API Guide Selection and Related Frequently Asked Questions for More Information. Alternatives to this feature include:

• mysqli_connect ()
• PDO :: __ construct ()

You are better off using PDO or MySQLi . Any of these 2 will be sufficient as compatible libraries when using prepared statements.

Entrusting the user input without prepared instructions / disinfection, this is how to leave your car in a bad area, unlocked and with the keys in the ignition. You basically say, just go in and take my goodies

You should never , and I mean never, trust user input. If you do not want this:

Regarding the data and its storage, as indicated in the comments, you can never and never should trust any user-related entries. If you are 101% sure that the data used to manage the specified databases / values ​​is hardcoded in your application, you should use the prepared instructions.

Now about why you should use prepared statements. It's simple. To prevent SQL injection, but in the most direct way. The way the prepared operators work is simple, it sends a request and data together, but separately (if that makes sense, haha). I mean the following:

 Prepared Statements Query: SELECT foo FROM bar WHERE foo = ? Data: [? = 'a value here'] 

Compared to your predecessor, where you truncated a query with data, sending it as a whole - in turn, this means that it was executed as a single transaction that caused SQL Injection vulnerabilities.

And here is an example of a pseudo PHP PDO to show you the simplicity of prepared statements / bindings.

 $dbh = PDO(....); // dsn in there mmm yeahh $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); $stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value); // insert one row $name = 'one'; $value = 1; $stmt->execute(); 

^{Adapted from the PHP manual for prepared PDO statements}

---

Read More

• How can I prevent SQL injection in php?
• What is SQL injection? ( ^{Simple terms} )

Answer 2

TL; DR Use prepared statements in 100% of cases if your application accepts any user input

---

You seem to have a little confusion. Do not use mysql_* ; mysql_* functions are mysql_* , deprecated and insecure. Use MySQLi or PDO instead. Secondly, mysql_num_rows has nothing to do with prepared statements and is not a PDO function at all. Before executing the query, you prepare the expression, and not after it, when you want to count the lines.

As for when to prepare statements, @ Mike'Pomax'Kamermans nailed it in the comments. If you ever, even once, use any data that has ever been affected by a user - even a supposedly trusted user - or generated by any type of third-party or third-party application, including a browser, using prepared statements. Only if 100% of your data is hard-coded or generated entirely by your code (for example, a simple counter variable), can you trust it.

For example, you cannot trust:

• Usernames
• Passwords
• Email Addresses
• User Comments
• Phone numbers
• Dates
• Search strings
• Browser Client Strings
• Credit card numbers
• File Names to Download
• And any other type of input created by the user or that the user can manipulate.

You have to check everything (for example, check that the email address is indeed an email address) before putting them into the database, of course. But even then, using prepared statements, this is a safe way.

Answer 3

Mysql_* already deprecated, so it’s better to switch mysqli_* or PDO

To prevent sql (mysql) injection : - How to prevent SQL injection in PHP? .

And prepared statements (these are SQL statements that are sent and analyzed by the database server separately from any parameters.) Use the query data for each user.

for example, when sending data that matches / receive records in db with a request. so when you run a request with form data.

Answer 4

There are two solutions for this:

01- Use prepared messages

To prevent SQL injections, we will need to use something called prepared statements that uses related parameters. Prepared statements do not combine variables with SQL strings, so an attacker cannot modify an SQL statement. Prepared expressions combine a variable with a compiled SQL statement, which means that SQL and variables are sent separately, and the variables are interpreted as strings, not part of the SQL statement.

02- Prepared expressions with mySQLi.

Using the methods described below, you do not need to use any other SQL injection filtering methods, such as mysql_real_escape_string (). This is due to the fact that using prepared statements it is impossible to perform normal SQL injection.

eg -

 $name = $_GET['username']; if ($stmt = $mysqli->prepare("SELECT password FROM tbl_users WHERE name=?")) { // Bind a variable to the parameter as a string. $stmt->bind_param("s", $name); // Execute the statement. $stmt->execute(); // Get the variables from the query. $stmt->bind_result($pass); // Fetch the data. $stmt->fetch(); // Display the data. printf("Password for user %s is %s\n", $name, $pass); // Close the prepared statement. $stmt->close(); } 

You can find more information about this form - http://www.wikihow.com/Prevent-SQL-Injection-in-PHP