Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the SQL-Interp CPAN distribution.

Report information
The Basics
Id:
53598
Status:
resolved
Priority:
Low/Low
Queue:

People
Owner:
MARKSTOS [...] cpan.org
Requestors:
MARKSTOS [...] cpan.org
Cc:
AdminCc:

BugTracker
Severity:
Wishlist
Broken in:
1.10
Fixed in:
(no value)



Subject: wish: a "strict" mode to force bind variable usage.
As expressed here: http://www.perlmonks.org/index.pl?node_id=815974 SQL::Interp is an excellent tool to help build SQL. It can be especially pleasant to use from DBIx::Simple. Combining the two, you can do this: $result = $db->iquery("SELECT * FROM foo WHERE a = ",\$b) It's easy to read, and $b will automatically be translated into a bind variable. My concern is that on a number of occasions I have seen well meaning programmers do this instead: $result = $db->iquery("SELECT * FROM foo WHERE a = ",$b) The difference is the backslash before the $b. The rub is that the code produces the same result either way, but the second format has possibly introduced an opportunity for a SQL injection attack. What suggestions do you have for how SQL::Interp might detect such cases, so that it can warn or die? Here are some brainstorms I've thought of, possibly activated only when a "strict mode" is enabled. - Scalars are no longer allowed at all. Instead, you would use something like: "sql('SELECT * from foo WHERE a = '),\$b" - Assume any two strings in a row is a mistake...you could always concatenate them instead. - Parse the SQL sufficiently to understand where bind variables *should* be and then make sure they are used there. Each option has its drawbacks, but does one stand out to you as being better? What other ideas can you think of to address this?
I propose implementing this with the second option: "no two strings in a row are allowed". Here's a bit more detail about how it shakes out: Rule: No two strings in a row. If you need that... concatenate them instead OK: "SELECT * FROM users WHERE", { user_id => 1 } "SELECT * FROM users WHERE user_id =", \1 Safe in lax mode, but fails strict mode anyway: "SELECT * FROM users WHERE", "user_id = ", \1 Not Safe: Caught by strict mode: "SELECT * FROM users WHERE user_id = ", 1 Strict mode still can't catch these cases: # direct interpolation to a string, defeating the purpose # of using SQL::Interp, and fairly easy to spot "SELECT * FROM users WHERE user_id = $one", ### Further comments before coding begins?
Subject: proposed "strict mode" for SQL::Interp
RT-Send-CC: spamcollector_cpan@juerd.nl
I've uploaded 1.11-TRAIL for comment which includes a proposed implementation of "strict mode" support. Here's what "Changes" says about it. Feedback welcome. ### - Add sql_interp_strict() as a first stab at an API for adding a strict mode. Switched to using Sub::Exporter and Exporter. This allows you to do this: use SQL::Interp 'sql_interp_strict' => { -as => 'sql_interp' }; So that strict mode is enabled throughout your code. Before you do that, read the docs on sql_interp_strict-- some of your code may need to be refactored for it work, so you may want to start with the incremental approach. While sql_interp() will work as before for backwards compatibility, you can do this to rename them on import now: use SQL::Interp 'sql_interp' => { -as => 'sql_interp_insecure' }, 'sql_interp_strict' => { -as => 'sql_interp' }; If you would like a shortcut for this in the form of SQL::Interp::Strict, let me know.
Has long been implemented now, marking as resolved.


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.