PHP SQL injection prevention without parameter bin

问题:

Possible Duplicate:
How to prevent SQL injection in PHP?

I am working for a video streaming website for my college library. I am using PHP and MySql. I have not used any parameterized queries in this project.

Recently I came to know about SQL injections. Now that my code is almost done and I have to submit the project in the next two days, how can I now ensure that my code is not SQL injection prone?

Converting the whole thing in to a parameterized interface is what I can't do now. What should I do now to avoid SQL Injections on my website?

回答1:

The basic idea to prevent SQL injections (if not using Prepared Statements) is to escape your data.

When you inject some expected integer value into an SQL query, make sure it's an integer, using intval().

When you have a decimal/numeric field in your table, use floatval().

And when you have a string (char, varchar, text) field in your table, use the function provided by your API to escape strings :

• mysql_real_escape_string()
• mysqli_real_escape_string()
• PDO::quote()

回答2:

I really recommend that you go back and do it right with parameterized queries. It is the only solid path towards security. It likely won't take too long to do this once you get started.

You should also know that websites are never "finished". When you launch a site, your work has just begun. Fixing security troubles as you learn about them is part of it, and this is no different.

回答3:

You'll want to make sure any user provided inputs that get used in SQL queries are escaped using the PHP function mysql_real_escape_string and if you are letting people submit text to run htmlentities on the provided text so XXS isn't possible. If possible, white-list user provided input and discard anything else

This is just touching the surface of what you can do but look into query escaping and preventing cross site scripting.

回答4:

Use PDO (or alternatively mysqli or some abstraction layer) and prepared statements.

Quick example:

$pdo = new PDO($dsn);
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute(array($unsafe_id));
$name = $stmt->fetchColumn();

In this example, $unsafe_id will be safe to use. To quote the manual page:

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.