If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

How do I Prepared Statements for PHP/MySQL SQL Injection Prevention?

I have a pages setup that seems to work fine, but I want to get some professional feedback as I am a bit of a newb at the prepared statement usage. I have a PHP page that uses several fields from a MySQL database table in various places. Can I place the prepared statement at the top of my page, then use variables within page, and then close at bottom of page? Here is my example...

Protecting against SQL Injection with PHP - MySQL

It is relatively easy to protect against a SQL Injection attack on a web application with a database. The best way is to ensure that all numeric type variables used in a Query are in fact numbers, before running the query. It is good that you are concerned about SQL injection as some serious and large scale thefts of credit card information have been commited using the SQL Injection attack on the internet.

Usually a web page is vulnerable to SQL Injection if it has the following format, with a URL numeric variable is not properly validated to be a number, often URL variables are used in web applications. Like the following URL:

The URL variable 999 is the primary key ID of a database table and is an integer type. This is often used in web applications on the internet, including web apps made with ASP .NET and ColdFusion. The problem occurs when a query like the following, in our example file RegisterUser.php, uses the UserID passed into the page as URL variable.

$dbquery = "SELECT * FROM tblUser WHERE UserID = " . $_GET['UserID'];

In this instance with the URL above, the query would evaluate to the following string:

SELECT * FROM tblUser WHERE UserID = 999

However, the risk for SQL Injection comes from the URL variable UserID, a malicious user could make the URL variable equal to an arbirtrary SQL command like the following:

WHen this is evaluated by the PHP code, it will turn into the following string:

SELECT * FROM tblUser WHERE UserID = 0;Drop Table tblUser;

This is a valid SQL statement and will be executed by the database, as you can see, in this manner the maliscious user can add arbitrary SQL statements to a web page and have those statements executed by the DB. Even worse, if the DB query results are dynamically displayed on the webpage, the malicious user can also see all the results of the Querys they are adding to the page. In effect, the malicious user is granted total control of the database in this manner.

This kind of attack was has been used by criminals to steal credit card numbers from web exposed Databases, especially DBs that are used in web based credit card transactions.

The kind of SQL injection described herein usually only works with Numeric database column types, for string types there is not much risk because the data passed into the URL variable will be enclosed within quote marks within the SQL Query, so any SQL command inside the quotes will not be executed by the DB.

The best way to protect against SQL injection is to make sure all numeric value variables that are to be used in a SQL Query are validated to make sure they are only numbers. There are many different ways to do this in PHP.

Hope this helps, also SQL injection can be used in web forms where a numeric type in the form is not properly validated to ensure it is a number.

Prepared Statements, bind_result and echo variables

Thank you for reply Michael. So how does this code look to you for secure GET retrieval of a string? Am I using it correctly to receive a single resultset? Also, the PHP page uses several fields from a MySQL database table in various places. Can I place the prepared statement at the top of my page (SEE BELOW), then use variables within page, and then close at bottom of page? Is there a better way?

The kind of SQL injection described herein usually only works with Numeric database column types, for string types there is not much risk because the data passed into the URL variable will be enclosed within quote marks within the SQL Query, so any SQL command inside the quotes will not be executed by the DB.

Sorry but that's not true. If you do no validation on the string, the user can still insert their own ' or " and end the string, thus allowing anything following that quote to be counted as a sql command. Even if you filter out quotes from the url var there are still ways to get encoded forms through. However, with that said, you should also be using quotes around numeric variables in a sql command, its perfectly valid.
For true protection from both xss and sql injection attacks you shoudl combine escaping with some sort of whitelist/blacklist filtering system. Escaping alone can still be compromised in certain situations.

I could be wrong but i think prepare does the same type of escaping as the mysqli_real_escape_string function. If so, then it will probably still only offer the same amount of security as before.
Although i should be clear, in my previous post i was replying specifically to michael's post. Technically escaping is better than nothing, and is sufficient in most cases, so don't worry too much about what i said earlier. However, there are ways around escaped strings, but it is up to you if you want to be paranoid enough to worry about it. Though, i personally would worry about it and make sure you follow the best practices. Here is a good quickie on how mysql's escape functions can fail, sometimes through incorrect usage like not using quotes around numerical results, like mentioned in my previous post.http://www.webappsec.org/projects/articles/091007.shtml

If you use prepared statements with the correct place-holder type for each external value, then you should be OK, as numeric type place-holder values will essentially be cast to that type (so "1; drop table users;--" will be converted to "1") and string types will be escaped and quoted (so the preceding example would become "'1\; drop table users\;--;", which may not be particularly desirable but will not execute the attempted injection).

You do not want to both escape a value and then use it in a prepared statement place-holder value, as it will be doubly escaped, and you'll end up with unwanted back-slashes stored in the DB. (The above example would then become "'1\\\; drop table users\\\;--'", which would be stored in the DB as "1\; drop table users\;--".)

However if all user values are sent via bind_param then it will be cleaned for you. In essence you're sending to MySQL a statement to be prepared, all commands sent after that are just the values being sent, not the entire query.

Also, bind_params is a reference, not a value, so changing the value of $value and executing again will change the results.

However if all user values are sent via bind_param then it will be cleaned for you. In essence you're sending to MySQL a statement to be prepared, all commands sent after that are just the values being sent, not the entire query.

Also, bind_params is a reference, not a value, so changing the value of $value and executing again will change the results.