[ic] sql filter not 100% safe for MySQL

[Mike Heins]

Quoting Daniel Davenport (ddavenport at newagedigital.com):
> 
> > [mailto:interchange-users-bounces at icdevgroup.org]On Behalf Of Mike Heins
> > Quoting John:
> > > Perhaps [query] won't execute 2 SQL statements separated by a semi-
> > > colon? So perhaps there is no risk of SQL injection?? But, it is
> > > possible to create a bad SQL statement in this way and so generate
> > > an error.
> >
> > Query will not execute two statements, but it would certainly be
> > possible to create a subquery situation.
> >
> > I wonder if MySQL has a way to ensure that \' is not interpreted as
> > a single quote? That would be the best way to solve this.
>
> It might be the easiest way outside of interchange, if it exists at
> all...but the mysql docs don't mention whether it exists, and my tests
> indicate that even "--sql-mode=ANSI_QUOTES" doesn't seem to disable
> backspace quoting (on mysql 4.0.17-max/win32...ymmv).

ANSI_QUOTES just means that you can't use double quotes to surround
a string literal.

>
> Would it be difficult to add a "table" parameter to the sql filter? If
> set, the parameter would indicate which table the query will end up
> going to. The filter could then tell the appropriate DB handler to
> quote the value, thereby making it safe for any DBI table that
> interchange knows about.

You could do [filter op="sql.table"]....

CodeDef sql Filter 1
CodeDef sql Routine <<EOR
sub {
	my($val, undef, $table) = @_;
	my $db;
	my $mysql = 1;
	if($table and $db = dbref($table)) {
		$mysql = $db->config('DSN') =~ /^dbi:+mysql/i;
	}

	if($mysql) {
	    my %lit = ( '\\\\' => '\\', '\\\'' => "'" );
	    $val =~ s/(\\.)/$lit{$1} || $1/eg;
	}
	$val =~ s/'/''/g;
	return $val;
}
EOR

That makes:

    [filter op=sql.products]'The don\'t will do'[/filter]<br>
    [filter op=sql.variable]'The don\'t will do'[/filter]<br>

produce:

    ''The don''t will do''
    ''The don\''t will do''

>
> Short of that, the only purely interchange solution might be to
> implement a mysql filter. That's ugly, though, as it requires that the
> coder always specify which tables are which types, and that could
> potentially make it a huge mess to move tables from one db type to
> another.
>
> There is another way, but it's not very pretty, and it's an all-or-
> nothing
> deal.
> 
> $data =~ s/(['"\\<>&\[])(?{local $__ord = ord($1)})/&#$__ord;/;
> 
> This will html-entity-ify quotes, backslashes, and HTML/ITL special
> characters all in one shot. You can change which characters are escaped by
> modifying the list inside the brackets.  (Don't mess with '&', though, or
> you won't be able to reliably unmangle your data.)  One side benefit of this
> type of escaping is that you can filter the HTML characters <>"& as well, so
> that's one problem to worry less about--all the browsers i've dealt with
> display the characters correctly, and even pass the unescaped sequence back
> when forms are submitted.
> 
> To unmangle it...
> 
> $data =~ s/&#(\d+);(?{local $__chr = chr($1)})/$__chr/;
> 
> Data mangled in this fashion won't be easily searchable unless you can
> mangle your search strings the same way.

I don't think this is the issue so much as what the decoded variable
value comes out to.

Hmm. This is decidedly inconvenient behavior for MySQL -- a case of
a convenience method coming back to bite you.

I would like to allow

	[query
		sql="select field from table where foo = ? and bar = ?"
		arg.0="[cgi foo]"
		arg.1="[cgi bar]"
	    ]

but unfortunately the array-based args don't handle included ITL.
This would be the safest way to do it -- to have DBI do the quoting
for you as needed.

I will think about this and see if an epiphany happens. Until then,
defining a mysql filter is probably the way to go.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

Prove you aren't stupid.  Say NO to Passport.