Asked  7 Months ago    Answers:  5   Viewed   52 times

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.

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?




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 enter image description here

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

SQL Injection

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;

Taken from PHP Manual for PDO Prepared Statements

More Reading

  • How can I prevent SQL-injection in php?
  • What is SQL-injection? (Simple Terms)
Tuesday, June 1, 2021
answered 7 Months ago

The History

This was my first Stackoverflow answer. A lot has changed since, specially the deprecation and removal of the mysql API. Even if you are still on php 5.6, the mysql_* api should not be used. Now PDO or mysqli are the only options to choose. PDO is better to lots of reasons.

Are prepared statements cached across page loads?

I've read some conflicting reports about what PHP's mysqli or PDO libraries do? Do either of them cache the prepared statement across script execution?

The same prepared statement will not be used in between page loads. It has to be prepared every time. If squeezing every large millisecond matters, a stored procedure might be a good idea (assuming you have a complicated query).

For large inserts (thousands of rows) A bigger boost can probably be gained by dumping your data into a text file and loading it with LOAD DATA IN FILE . It's a lot faster than a series of inserts.

The original answer

The truth of the matter is that sometimes mysqli is faster and at other times mysql api is faster. But the difference is really really small. If you look at any of the performance tests on the web the difference is really just 10 - 20 milliseconds. The best way to boost performance is to optimize table design.

Many of the tests that 'prove' the older api to be faster conveniently forget that for maximum security mysql_real_escape_string() should be called for each variable used in the query.

Queries are cached by the server, if and only if the data on all the tables that are used in the query have remained unchanged.

Await another update with actual numbers

Wednesday, March 31, 2021
answered 9 Months ago

It seems that you have Magic Quotes enabled. But you better disable this option or revert them. mysql_real_escape_string is more secure.

Wednesday, March 31, 2021
answered 9 Months ago

"NavigationWindow does not store an instance of a content object in navigation history. Instead, NavigationWindow creates a new instance of the content object each time it is navigated to by using navigation history. This behavior is designed to avoid excessive memory consumption when large numbers and large pieces of content are being navigated to. Consequently, the state of the content is not remembered from one navigation to the next. However, WPF provides several techniques by which you can store a piece of state for a piece of content in navigation history...."

Monday, August 16, 2021
answered 4 Months ago

You always need to use white-lists for stuff like table- or column names, whether you use prepared statements or the mysql escape functions.

The problem is that table names and column names are not quoted in single or double quotes, so if you use a function that specifically quotes these characters (and some more of course...), it will do nothing for your table name.

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table. Nothing in this string will get escaped by mysql's escape functions but it is definitely a string you would want to check against a white-list.

Apart from that the mysql escape functions have a problem with character sets that can render them useless, so you are always better off with prepared statements.

Thursday, November 4, 2021
Vinko Vrsalovic
answered 4 Weeks 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 :