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?
However, prepared statements only persist per connection. Unless you're using connection pooling, there would be no benefit if you're only doing one statement per page. Trivially simple queries would not benefit from the more efficient transport format, either. Personally I wouldn't bother.
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.
Statement – Used to execute string-based SQL queries. PreparedStatement – Used to execute parameterized SQL queries.
Prepared statements are statement already interpreted, the DBMS change parameters and the query starts immediately. This is a feature of certain DBMS and you can achieve fast response (comparable with stored procedures). Parametrized statement are just a way you compose the query string in your programming languages.
tl/dr
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With