#prepared statements

In the below video tutorial, take a closer look at JDBC PreparedStatement with example and Statement vs PreparedStatement. by Ram N · Mar. 20, 22 · Java Zone · Tutorial Source link

There has been a lot of talk lately about MySQL stored procedures and prepared statements and I thought I would give you a little run down on the whens, whys, and hows of each of these features.

First of all, the information to follow is very general and your situation may differ based on the project, code version available to you, and your skill level. A huge resource that I have used when learning this information is Larry Ullman's books on the topic so pick them up on Amazon if you want to get a better grasp of the information presented in this blog post.

Prepared Statements

When? Prepared statements are a great option when writing php code that will be querying the database with the same query over and over again but with different variables. For example if you are running a SELECT query that must retrieve an entire row based on email=$email. When that query needs to be run multiple times in the same system it is good to use a prepared statement.

Why? Prepared statements allow the database to cache the query improving the performance. They also because the prepared statements separate the variables from the query you do not have to take extra steps to prevent SQL injection attacks

How? First you define your query:

$q = 'SELECT id, username, type FROM users WHERE (email=? and password=?)';

Next you call the mysqli_prepare() function:

$stmt = mysqli_prepare($dbc, $q);

Then you bind the variables:

mysqli_stmt_bind_param($stmt, 'ss', $email, $pass);

The key here is the second argument (ss) this is an indicator of the formats of the various placeholders. There is one letter per variable (in our case ss where one s is for $email and the second s is for $pass). Other types of indicators would be i for integer, d for decimal, and b for blob. The s in our statment stands for string.

Make sure you have defined the variables prior to calling the next statement that executes the query:

mysqli_stmt_execute($stmt);

After that you will have to store the results and bind them to variables you will be using in the script:

mysqli_stmt_store_result($stmt);

mysqli_stmt_bind_result($stmt, $id, $username, $type);

mysqli_stmt_fetch($stmt);

Now you have the variables $id, $username, and $type all filled and ready to use in your script. Of course if your webhost does not have MySQL configured to handle prepared statements simply use the normal mysqli_query() but make sure to escape all user input to prevent against SQL Injection attacks.

Just got back from vacation a few days ago.  I went to vegas, dropped a lot of money lol, but had fun.  Tonight I basically was working on the custom pages again and focus on making it able to add a custom page with HTML but not PHP.  That led to me searching google about whether real_escape_string was sufficient enough to block sql injection.  One of the first pages that came up was this:

http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input

Finding this page led to basically 3 hours of figuring out how to use prepared statements.  There is a PDO class that I could have used that would probably have made it easier, but that would also require a lot of rewriting of code.  The MySQLi class that I was using has the ability to use prepared statements but its a little different.  There is a prepare method and a bind_params method that you need to use to build an sql query with MySQLi.  PDO has almost the same thing except that PDO lets you use multiple bind_params, whereas MySQLi only lets you bind ONE parameter at a time.  I quickly learned that you can use another function to get around this called call_user_func_array, but still wasn't working for me.  I eventually figured it out and got it to work but it took a really long time.  

Sorry for the super technical post but that's all I have right now.  I have 2 database methods that I want to add prepared statements to, the one that inserts and the one that updates.  The insert one is basically done, super tired now though so will finish tomorrow.

*[XSS]: Cross Site Scripting *[SQL]: Structured Query Language *[PDO]: PHP Data Objects *[REST]: Representational State Transfer *[MVC]: Model-View-Controller *[LAMP]: Linux, Apache, MySQL, PHP ### How being prepared can prevent needing to escape ### **Note: I am **not** a security expert, but I have become enamored and fascinated by Web Security. This is what I have learned from reading and personal experience. If you know of a better way, or wish to correct something, *please do*! I am more than happy to learn and amend.** There has been a bit of talk on Forrst about XSS and the issue of escaping data, stripping input, and saving things to databases[^1].[^2] The two main security issues in play here are SQL Injection and Cross Site Scripting (XSS). This is going to be slightly in depth, so I am going to split it into two smaller and more digestible chunks. The first will deal with the basics of SQL Injection and parameterized queries, while the second will deal with XSS and escaping output. To begin, I'm going to weave a tale of a frustrated developer. You are the frustrated developer, working at a new Web 2.0 company. Your boss says she wants more user participation, because user participation is super 2.0, and has tasked you to create a comments section for the company's custom coded blog. You are on a shared hosting LAMP stack (boss wanted to go the cheap and easy route) and so you are stuck dealing with a relational database (MySQL) and PHP. You decide to make your form super simple, just the person's name [`name="name"`], their e-mail (you obviously want to grab their gravatars!) [`name="email"`], and their comment [`name="comment"`]. So you start out coding your PHP to interact with your MySQL database. You've got it all set up to connect properly to your aptly named `comments` table and now you are ready to store those pieces of data in the database. You don't think you have to worry security (or don't know too much about it) so you go the super simple way and make

// The comment data has been POSTed to this comment processing script $comment_name = $_POST['name']; $comment_email = $_POST['email']; $comment_body = $_POST['comment']; $query = "INSERT INTO comments VALUES ('" + $comment_name + "', '" + $comment_email + "','" + $comment_body + "')"; mysql_query($query);

And you think **bam, done**. But along comes a smart hacker, and figures that your code more than likely looks like that, so he writes a comment that looks like Name: Smart Hacker Email: [email protected] Comment: You are about to get hacked '); DROP TABLE comments-- Which then creates a query that looks like

INSERT INTO comments VALUES ('Smart Hacker', '[email protected]', 'You are about to get hacked '); DROP TABLE comments-- ')

When your query runs this, you no longer have a comments database! This is a properly formed SQL query that first inserts a value into the database and then drops the entire table (the `--` is a SQL comment telling it to ignore the rest of the statement). **This is known as a [SQL Injection](https://www.owasp.org/index.php/SQL_Injection).** Albeit, this is a *very* simplistic version of one, but the basic concept is the attacker can execute potentially malicious SQL and interact with your database without you knowing. Whether that's logging in as an admin, changing someone's password to compromise their account, dropping your tables. It's **bad news bears**. The proper way to prevent against this is to use Parameterized Queries. In PHP, this is conveniently contained within [PDO](http://php.net/manual/en/book.pdo.php), available in PHP5. You read up on PDO and figure out that you can rewrite that query a parameterized query as

try { // You've already created a PDO connection saved with a handle in $handle $comment_name = $_POST['name']; $comment_email = $_POST['email']; $comment_body = $_POST['comment']; $comment_query = $handle->prepare('INSERT INTO comments (name, email, comment) value (:name, :email, :comment)'); $comment_query->bindParam(':name', $comment_name, PDO::PARAM_STR); $comment_query->bindParam(':email', $comment_email, PDO::PARAM_STR); $comment_query->bindParam(':comment', $comment_body, PDO::PARAM_STR); $comment_query->execute(); } catch (Exception $e) { echo "Something somewhere didn't work. Try again, please?"; }