NYCPHP Meetup

[John Campbell]

On Fri, Jun 26, 2009 at 1:33 AM, Konstantin Rozinov<krozinov at gmail.com> wrote:
> On Thu, Jun 25, 2009 at 6:04 PM, John Campbell<jcampbell1 at gmail.com> wrote:
>> On Thu, Jun 25, 2009 at 5:44 PM, Eddie Drapkin<oorza2k5 at gmail.com> wrote:
>>> Wait, are you advocating //against// prepared statements?
>>
>> Not at all, but when using mysql, you should emulate them.  I am
>> actually all for "prepared" style queries, if I ever see
>> "mysqli_real_escape_string" in someone's code, I immediately write the
>> person off as clueless.
>>
>
> What's so clueless about using mysql_real_escape_string()?  I would be
> interested to find out.

String interpolation (using double quoted strings) is inherently
dangerous.  All it takes is forgetting to call
mysql_real_escape_string, *once* and you have a nasty vulnerability.
It also makes for really bloated code.  To fix the problem, you just
have to stop using double quoted strings.

consider this:
$sql = "SELECT title from posts WHERE YEAR(posted_at)=$year"

Looking at the above code, you have no way to know if that is safe or
not.  You cannot reason about it unless you trace the "$year" back to
its source.  Are you sure it can't become:
$sql = "SELECT title from posts WHERE YEAR(posted_at)=2008 UNION
SELECT id as title from sessions";

Now an example without string interpolation:
$sql = sqlf('SELECT title from posts WHERE YEAR(posted_at)=%d',$year);

We know the above code is safe regardless of the value of $year.  The
internals of this 'sqlf' function may call "mysql_real_escape_string",
or it may use prepared queries, but the point is that we can reason
that this style of coding will never let us down.  If you want a good
"sqlf" type function, see wordpress,drupal, or pretty much any open
source project that uses a database.  They all write their queries
this way.

If I am looking at someone else's code and it is loaded with
"mysql_real_escape_string", I must just blindly trust that they
haven't made one mistake anywhere in the code.

To me the issue prepared, vs. non prepared.  It is that escaping
strings + string interpolation is inherently prone to mistakes.

Regards,
John Campbell