473,322 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Tricky Error

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:

LIKE%28%27%25415%25%27%29

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

Which is GREAT... all that makes sense.

Unfortunately, when I rawurldecode that URL, I get:
LIKE('A5%')

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

~D

Jul 17 '05 #1
15 2182
dr********@gmail.com wrote:
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.

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");
exit;
}

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

fclose($handle);
}
$url = "existingurl?query=$cached_filename;
-------------
$query = $_GET['query'];
$cached_query = "cachedqueries/$query";
if(file_exists($cached_query)){
$sql = file_get_contents($cached_query);
} else {
$sql = "SELECT * FROM table";
}
--------------------------------
J Wynia
Myriad Intellect, Inc.
"Web technology that earns its keep."
www.myriadintellect.com
Jul 17 '05 #2
dr********@gmail.com wrote:
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.

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");
exit;
}

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

fclose($handle);
}
$url = "existingurl?query=$cached_filename;
-------------
$query = $_GET['query'];
$cached_query = "cachedqueries/$query";
if(file_exists($cached_query)){
$sql = file_get_contents($cached_query);
} else {
$sql = "SELECT * FROM table";
}
--------------------------------
J Wynia
Myriad Intellect, Inc.
"Web technology that earns its keep."
www.myriadintellect.com
Jul 17 '05 #3
dr********@gmail.com wrote:
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.


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.

HTH

C.

Jul 17 '05 #4
dr********@gmail.com wrote:
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.


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.

HTH

C.

Jul 17 '05 #5
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.

~D

Jul 17 '05 #6
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.

~D

Jul 17 '05 #7
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
project.

~D

Jul 17 '05 #8
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
project.

~D

Jul 17 '05 #9
dracolytch wrote:
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.
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 = 's******@example.com';

which, when injected can become

WHERE email = 's******@example.com'; DROP TABLE members; --';

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.
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.

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.

~D

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
appropriate.

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.
Jul 17 '05 #10
dracolytch wrote:
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.
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 = 's******@example.com';

which, when injected can become

WHERE email = 's******@example.com'; DROP TABLE members; --';

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.
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.

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.

~D

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
appropriate.

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.
Jul 17 '05 #11
On Tue, 10 May 2005 07:10:07 -0700, dracolytch wrote:
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:

LIKE%28%27%25415%25%27%29

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

Which is GREAT... all that makes sense.

Unfortunately, when I rawurldecode that URL, I get: LIKE('A5%')

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


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

Jul 17 '05 #12
On Tue, 10 May 2005 07:10:07 -0700, dracolytch wrote:
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:

LIKE%28%27%25415%25%27%29

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

Which is GREAT... all that makes sense.

Unfortunately, when I rawurldecode that URL, I get: LIKE('A5%')

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


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

Jul 17 '05 #13
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.

~D

Jul 17 '05 #14

Mladen Gogala wrote:
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.


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

Jul 17 '05 #15
On Wed, 11 May 2005 15:03:53 -0700, BKDotCom wrote:

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


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:

http://www.rittman.net/archives/000832.html
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.

<html>
<head>
<title>Resize File</title>
</head>
<body bgcolor="#EFECC7">
<center>
<h2>Resize File</h2>
<br>
<hr>
<?php
require_once ('helper.inc.php');
require_once "HTML/Form.php";
require ('csr2html.php');
session_start();
$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->addBlank(3);
$form->addHidden('file', $FNO);
$form->addHidden('type', $TYPE);
$form->addText('size', $row[0].':', $row[1].'M');
$form->addSubmit("submit", "Resize");
$form->display();
} elseif (!empty($SIZE)) {
$RSZ = "alter database $TYPE"."file $FNO resize $SIZE";
$db->Execute($RSZ);
$db->close();
$INVOKER = $_SESSION['invoker'];
header("location: $INVOKER");
}
}
catch(Exception $e) {
die($e->getTraceAsString());
}
?>
<hr>
</center>
</body>
</html>
--
Demagogue: One who preaches a doctrine he knows to be untrue to
men he knows to be idiots.
H.L. Mencken

Jul 17 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: dracolytch | last post by:
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()...
4
by: Bung | last post by:
Hi, I have a tricky sql statment I have to write (tricky for me) and I am stuck. I'm having trouble with the following problem. Table1 (Column a, Column b, Column c) Table2 (Column a, Column...
25
by: PyPK | last post by:
What possible tricky areas/questions could be asked in Python based Technical Interviews?
5
by: Danny | last post by:
Hi there I need help with a tricky problem. I have a 2 dimensional array with qualities such as ball size, ball color, ball weight. Now I have to print out all the possible combinations of...
13
by: Steve Jorgensen | last post by:
== On Error Resume next, and Err.Number == If you want to call one of your procedures from another procedure, and check for errors afterward, you mayimagine that you should write code something...
8
by: pras.vaidya | last post by:
Hi , below given question was asked to me during an interview and i figured it out little tricky . It would be a great help if anyone could solve it. Code : - main() { char...
6
by: Thomas Tomiczek | last post by:
Ok, working my way through a complex library conversion to .NET 2.0 and C# 2.0 with generics I am totally stuck on one thing -if anyone sees the issue I would be more than glad. The situation is...
7
by: VB Programmer | last post by:
I am using the BitBlt operation to capture various controls into jpegs. It's almost like a screen capture, but of just the control. (This is a VB.NET application.) Because of BitBlt limitations...
8
by: VB Programmer | last post by:
I would appreciate your assistance on this ASP.NET 2.0 site.... This is the wierd problem: While accessing the built in .NET functions for 'profiling' or 'membership' an error is generated (see...
2
by: aaron1234nz | last post by:
I am trying to insert new rows into a table but there are a few tricky things I have not been able to get my head around. 1. I need to insert a unique ID in each row. 2. I need to insert rows...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.