PHP SQL injection prevention without parameter binding

Question

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?

@Arihant, why can't you? Do you not have access to it anymore? — Brad, Apr 11 '11 at 19:52
Really, this is one of the most asked and best answered question only askable. Sometimes I feel like for some people the search field isn't displayed... — NikiC, Apr 11 '11 at 19:56
@Brad: I have very less time left. And some other basic modules are yet to be completed. — Arihant Nahata, Apr 11 '11 at 20:14
@nikic Can you help me in finding any of those having the same problem? — Arihant Nahata, Apr 11 '11 at 20:15

score 4 · Accepted Answer · answered Apr 11 '11 at 19:46

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 :

score 1 · Answer 2 · answered Apr 11 '11 at 19:45

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.

score 0 · Answer 3 · answered Apr 11 '11 at 19:45

0

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.

answered Apr 11 '11 at 19:45

Jake

2,471
15
24

This doesn't solve the problem. You should at least reference `mysql_real_escape_string()`. – Brad Apr 11 '11 at 19:46
Actually, what I mention are preventive measures. This is what he asks for and he even states he can't parameterize the queries. – Jake Apr 11 '11 at 19:51
Thanks for changing your post. I have undone my downvote. – Brad Apr 11 '11 at 19:52

score 0 · Answer 4 · answered Apr 11 '11 at 19:51

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.

PHP SQL injection prevention without parameter binding

4 Answers4

Linked