Tricky Error

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Good day all,
Ok, I have a pretty tricky problem that I need some help with.

I pass around search query information a fair amount (specifically
WHERE statements). Normally, I just rawurlencode() the buggers, and
pass them via the URL. I like having the where clauses in the URL,
because then someone can just bookmark the URL, or send it to a friend,
and I don't have to worry about a thing.

If someone does a search that requires a LIKE statement, the system
parses out the terms, and generates the LIKE statement, and passes the
LIKE statement to the search results page. This works great.

Almost all of the time.

Here's where the problem occurs:
If you search for a NUMBER, and the system uses a LIKE statement, the
string looks like this:

data LIKE('%415%')

Which is great... When I rawurlencode it, I get:


Looking at it more closely:
%25 = %
%27 = '
%28 = (
%29 = )

Which is GREAT... all that makes sense.

Unfortunately, when I rawurldecode that URL, I get:

It appears to be interpreting the %25 as a %, and then using that % on
the next two numbers... Ideas?


Re: Tricky Error wrote:
Quoted text here. Click to load it
  1. You should probably do some reading on "SQL injection" and tread
very carefully. Having SQL in your request (even if you're parsing it)
is playing with fire.
  2. You can accomplish the whole "bookmarkable URL" thing without
putting your SQL right on the URL.

Here's a way to do it. Cache the SQL statements that your script creates
from the search query to the filesystem. An easy way to do that is to
md5() the constructed SQL statement, create a file with that md5() value
and put the SQL statement into the file. Then, put query=md5string on
your URL's. When that parameter is present, check the filesystem for the
appropriate cached query and pull it in, using it instead of your
defaults. Quick sample snippets are below.

$sql = "SELECT * FROM table";
$cached_filename = md5($sql);
$filename = "cachedqueries/$cached_filename";
    if (!$handle = fopen($filename, 'w')) {
          die("Cannot open file $filename");

     if (fwrite($handle, $sql) === FALSE) {
        die("Cannot write file ($filename");

$url = "existingurl?query=$cached_filename;
$query = $_GET['query'];
$cached_query = "cachedqueries/$query";
    $sql = file_get_contents($cached_query);
} else {
    $sql = "SELECT * FROM table";
J Wynia
Myriad Intellect, Inc.
"Web technology that earns its keep."

Re: Tricky Error

To be honest, I've already done this analysis. The SQL in the URLs are
only the WHERE clauses. So, someone could go ahead and inject their own
SQL to create their own custom WHERE clause. All the more power to 'em.

This is not the kind of system where I'm doing my "Trust no input from
the user" diligence, I'm just organizing available data in an
accessable format.

True, your form of caching would solve the given problem, but I think
it may be over-solving the problem. It could also incur additional
maintenance issues once the cache reaches a couple thousand entries.


Re: Tricky Error

dracolytch wrote:
Quoted text here. Click to load it

Most of the "classic" examples of SQL injection *are* in the WHERE
clause. Many are within a specific column name portion of the WHERE
clause. One of those classic examples is:

WHERE email = '';

which, when injected can become

WHERE email = ''; DROP TABLE members; --';

Quoted text here. Click to load it

Given that the solution I mentioned takes less than 15 minutes, it's
hard to justify *not* taking that level of diligence. To me, not passing
anything important (like SQL, usernames, passwords, account information,
etc.) on the URL is my default level of diligence for any project I'm
putting on the web at large.

Quoted text here. Click to load it
I actually use variations of the code I posted to do caching of a LOT of
things. In most instances, I also wrap a date check in there or a file
count that cleans up "all files created before $timestamp" or
"everything but the latest 100". If the cache is going to be big, I make
this a seperate script and schedule it to be called daily, etc. It's
such a simple solution, I don't see how it's "over-solving" the problem.

I actually also frequently do this sort of caching on result sets, RSS
and other remote XML, etc. as well. Many of my caches run well into the
10,000-50,000 file range and still perform quite well. In most cases, it
also sped up the application in the process as getting the contents of a
specific file is quicker than connecting to a database or making a
remote connection to fetch content. In most of my implementations, using
something similar to what I posted, I actually wrap the database fetch
itself in a cache check. It's still based on an md5() of the query, but
instead of storing the query, I store a serialized version of the
returned array from the database. I add a quick additional check next to
the file_exists to also check that it's new enough and delete as

As long as you either add cleanup code or know the churn rate of the
data is so low as to not be an issue, there is no maintenance. I see
literally hundreds of apps on Sourceforge, Hotscripts, etc. that are
making dozens of calls to databases on every page load for data that
changes twice a year. That's a complete waste of database time.

Re: Tricky Error

Cleaning the cache then breaks the saved URLs, so getting the behavior
I want with a file-based cache is not as straightforward as you're
making it out to be. So, while your ideas are good, their place is not
in this application. Encrypting the clauses, as another submitter
suggested, appears to be a more appropriate solution.

As it is, since our projects are inernal to our organization via a
secured and encrypted network, the audience is implicitly trusted,
injection is a non-issue. Frankly, if someone outside of our audience
got in, they're not here to corrupt our phone list.


Re: Tricky Error wrote:

Quoted text here. Click to load it

SQL injection?

I use mcrypt to encrypt it using AES, then base64 encode it. The encryption
prevents people modifying the code (I actually encode($check .
$where_clause) so I can validate on $check). The base64 encode bit means I
don't need to worry about whether it is urlencoded or not (although the
'==' at the end gets stripped off if I include it in a URL without encoding
it, base64decode iterprets it just the same.



Re: Tricky Error

The base 64 encoding did the trick. I don't need to encrypt the
information at this point, but it does give me the additional
flexibility for easily hardening this code if reused in another


Re: Tricky Error

On Tue, 10 May 2005 07:10:07 -0700, dracolytch wrote:

Quoted text here. Click to load it

I read the whole debate and I agree with other people which fear the SQL
Injection. Also, when you assemble your SQL like that, you're not helping
your database, because it will have to perpetually parse the new
statement. With modern statistics based optimizers, it can become rather
expensive. My solution would be to use WHERE COLUMN LIKE '%:BVAR%' and
then bind the passed string to the variable :BVAR. The advantage of that
solution is that you will parse only once. Bind variables will also take
care of the SQL injection.

Demagogue: One who preaches a doctrine he knows to be untrue to
           men he knows to be idiots.
H.L. Mencken

Re: Tricky Error

Mladen Gogala wrote:
Quoted text here. Click to load it

Could you elaborate on "bind variables"?
what is this technique, or construct, or whatever you speak of?

Re: Tricky Error

On Wed, 11 May 2005 15:03:53 -0700, BKDotCom wrote:

Quoted text here. Click to load it

Below is an example, taken from a little tool for administration of
oracle databases. The bind variable is ":FNO", in the WHERE clause
of the SQL statement in the $INFO variable. In the "execute" part there is
additional argument array("FNO"=>$FNO) which "binds" SQL placeholder FNO
to PHP variable $FNO. That is know as using bind variables. As my database
of choice is Oracle, I'll point you to an article written by an oracle guy:

Bind variables were originally invented by IBM in their DL/I database
and have made their way in to SQL92 standard. That means that pretty much
any database can do bind. MySQL and PostgresSQL can both do that, at least
if used through ADOdb modules. For more about bind variables, consult your
database product manuals.

<title>Resize File</title>
<body bgcolor="#EFECC7">
<h2>Resize File</h2>
    require_once ('');
    require_once "HTML/Form.php";
    require ('csr2html.php');
    $DSN = $_SESSION['DSN'];
    $db = NewADOConnection("oci8");
    if (!empty($_GET['type'])) {
        $TYPE = $_GET['type'];
        $FNO = $_GET['file'];
    } elseif (!empty($_POST['type'])) {
        $TYPE = $_POST['type'];
        $FNO = $_POST['file'];
        $SIZE = $_POST['size'];
    } else die("File was called incorrectly!<br>");
    try {
        $db->Connect($DSN['database'], $DSN['username'], $DSN['password']);
        $INFO = "select file_name,ceil(bytes/1048576) as MB
           from dba_".$TYPE."_files
           where file_id=:FNO";
        $rs = $db->Execute($INFO, array('FNO'=>$FNO));
        $row = $rs->FetchRow();
        if (!empty($_GET['file'])) {
            $form = new HTML_Form($_SERVER['PHP_SELF'], "POST");
            $form->addHidden('file', $FNO);
            $form->addHidden('type', $TYPE);
            $form->addText('size', $row[0].':', $row[1].'M');
            $form->addSubmit("submit", "Resize");
        } elseif (!empty($SIZE)) {
            $RSZ = "alter database $TYPE"."file $FNO resize $SIZE";
            $INVOKER = $_SESSION['invoker'];
            header("location: $INVOKER");
    catch(Exception $e) {

Demagogue: One who preaches a doctrine he knows to be untrue to
           men he knows to be idiots.
H.L. Mencken

Site Timeline