How is this MySQL query vulnerable to SQL injection?

In a comment on a previous question, someone said that the following sql statement opens me up to sql injection:

select
	ss.*,
	se.name as engine,
	ss.last_run_at + interval ss.refresh_frequency day as next_run_at,
	se.logo_name    
from 
	searches ss join search_engines se on ss.engine_id = se.id
where
	ss.user_id='.$user_id.'
group by ss.id
order by ss.project_id, ss.domain, ss.keywords

Assuming that the $userid variable is properly escaped, how does this make me vulnerable, and what can I do to fix it?

Answers


Assuming it is properly escaped, it doesn't make you vulnerable. The thing is that escaping properly is harder than it looks at first sight, and you condemn yourself to escape properly every time you do a query like that. If possible, avoid all that trouble and use prepared statements (or binded parameters or parameterized queries). The idea is to allow the data access library to escape values properly.

For example, in PHP, using mysqli:

$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare("SELECT thing FROM stuff WHERE id = ?");
$statement->bind_param("i", $user_id); //$user_id is an integer which goes 
                                       //in place of ?
$statement->execute();

Every SQL interface library worth using has some kind of support for binding parameters. Don't try to be clever, just use it.

You may really, really think/hope you've escaped stuff properly, but it's just not worth the time you don't.

Also, several databases support prepared statement caching, so doing it right can also bring you efficiency gains.

Easier, safer, faster.


Need Your Help

Use continue or Checked Exceptions when checking and processing objects

java coding-style code-review

I'm processing, let's say a list of "Document" objects. Before I record the processing of the document successful I first want to check a couple of things. Let's say, the file referring to the docu...

How can i load data every time when an inner div of html page is changed?

html html5 jquery jquery-plugins

In my website i made a mainpage which has 3 divs: header, footer and main. Header div has some buttons which change main div. Means that page is loaded only once then i just change main div when some

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.