When should I use prepared statements?

 

Originally I used mysql_connect and mysql_query to do things. Then I learned of SQL injection, so I am trying to learn how to use prepared statements. I understand how the prepare and execute functions of the PDO class are useful to prevent SQL injection.

But, are prepared statements only necessary when a users input is stored into a database. Would it be okay to still use mysql_num_rows, since I don't really run the risk of being hacked into by using this function? Or is it more secure to use prepared statements to do this? Should I use prepared statements for everything that involves using mysql? Why?

I really appreciate any answers and feedback. Thank you.

tl/dr

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

mysql_* functions are deprecated. (Notice the big red box?)

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: choosing an API guide and related FAQ for more information. Alternatives to this function include:

  • mysqli_connect()
  • PDO::__construct()

    • You'd be better off using PDO or MySQLi . Either of those 2 will suffice as compatible libraries when using prepared statements.

    Trusting user input without prepared statements/sanitizing it is like leaving your car in a bad neighborhood, unlocked and with the keys in the ignition. You're basically saying, just come on in and take my goodies

    You should never , and I mean never, trust user input. Unless you want this:

    SQL注入

    In reference to the data and storing it, as stated in the comments, you can never and should never trust any user related input. Unless you are 101% sure the data being used to manipulate said databases/values is hard-coded into your app, you must use prepared statements.

    Now onto why you should use prepared statements. It's simple. To prevent SQL Injection, but in the most straight forward way possible. The way prepared statements work is simple, it sends the query and the data together, but seperate (if that makes sense haha) - What I mean is this: 

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

    Compared to its predecessor, where you truncated a query with the data, sending it as a whole - in turn, meaning it was executed as a single transaction - causing SQL Injection vulnerabilities.

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

    $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();

    Taken from PHP Manual for PDO Prepared Statements

    More Reading

  • How can I prevent SQL-injection in php?
  • What is SQL-injection? (Simple Terms)

    • TL;DR Use prepared statements 100% of the time if your app accepts any user input

    You seem to have a slight confusion. First, please don't use mysql_* ; the mysql_* functions are outdated, deprecated, and insecure. Use MySQLi or PDO instead. Second, mysql_num_rows has nothing to do with prepared statements and is not a PDO feature, anyway. You prepare the statement before you run the query, not after it when you want to count rows.

    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 touched by a user -- even a supposedly trusted user -- or is generated by any kind of third party or third-party application, including a browser, use prepared statements. Only if 100% of your data is hard-coded or generated entirely by your code (like 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 for uploads
  • And any other kind of input created by a user or that a user could manipulate.

    • You should validate all of these (for example, check that an email address is really an email address) before putting them in a database, of course. But even then, using prepared statements is the safe way to go.

    Mysql_* already has been deprecated so better to switch mysqli_* or PDO

    For prevent sql injection (mysql) :- How can I prevent SQL injection in PHP?.

    And prepared statements(These are SQL statements that are sent to and parsed by the database server separately from any parameters. ) use on your every user generated query data.

    like on posting data you matching/getting records to db with query. so mean when you fire a query with form data.

    链接地址: http://www.djcxy.com/p/93766.html

    上一篇: 防止没有MySQLi或PDO的SQL注入

    下一篇: 我应该什么时候使用准备好的陈述