Order by primary key

Hello all,

I'm building an Ajax table which fetches its contents from mySQL. The
idea is that the table displays x results on each page, and when you
click 'next page' it downloads the next x results and displays them.

The table uses an arbitrary SQL string to fetch results, and I'm stuck
on how to access just a small result set. Since the SQL is arbitrary I
can't use WHERE myfield = somevalue, because I don't know what
'myfield' is.

I'd like to substitue 'myfield' for the primary key of whatever table
I'm accessing.

So "select * from table where myfield < x and myfield > y" becomes
"select * from table where table_primary_key < x and table_primary_key
> y"

Is this possible?

Many thanks,
Matt.

Re: Order by primary key

bennett.matthew@gmail.com wrote:
> Since the SQL is arbitrary I
> can't use WHERE myfield = somevalue, because I don't know what
> 'myfield' is.

Yikes! If you are writing a browser-based app to execute arbitrary SQL,
you are opening yourself up to all sorts of mischief. I hope you're not
going to expose this application to the general internet.

Anyway, to answer your question, read about the INFORMATION_SCHEMA
(assuming you are using MySQL 5.0).

http://dev.mysql.com/doc/refman/5.0/en/information-schema.ht ml

Regards,
Bill K.

Re: Order by primary key

Hi Bill,

Thanks for getting back to me. The information schema page looks
useful.

Regarding the arbitrary SQL thing - no the application is not for
general internet use, but security should still be a concern. Actually
the long-term plan is to generate XML on the client which is then
transformed into SQL on the server.

However, to my mind this is almost as vunerable: the XML is simply a
retranslation of the SQL, so if you can manipulate one you can
manipulate the other.

Presumably I should build some server-side checking into the process,
to ensure whatever XML/SQL arrives is authorized to execute. I've not
really thought about how to do that yet. I guess it means keeping track
of the 'state' of the client. Do you have any comments / suggestions on
this? The application is being written with Google's Web Toolkit
(http://code.google.com/webtoolkit).

Thanks,
Matt.

Re: Order by primary key

bennett.matthew@gmail.com wrote:
> Presumably I should build some server-side checking into the process,
> to ensure whatever XML/SQL arrives is authorized to execute. I've not
> really thought about how to do that yet. I guess it means keeping track
> of the 'state' of the client. Do you have any comments / suggestions on
> this?

I am pretty conservative when it comes to constructing dynamic SQL on
the fly. I do not want to let SQL execute unless I'm the one who
designed the query. Who knows if the user will cause a horrendous query
to crash my server. It's very easy to do:

"SELECT * FROM tablename, tablename, tablename, tablename, tablename
ORDER BY 1"

Even if the table specified contains as few as 100 rows, the above query
will probably kill your server, as it tries to sort the result set of
10^10 rows!

So I think the "best practice" is to include only _values_ in your XML
request, not names of tables or names of columns.

So a good rule of thumb is to follow the same restrictions as exist when
you PREPARE the SQL statement, using parameter placeholders. Then when
you get the XML request, plug in data values when executing the prepared
query.

Query parameters for prepared statements can be values, but not table or
column names, and not any other syntax element. In other words:

prepare "SELECT * FROM tablename WHERE columname = ?" -- LEGAL,
parameter will be interpreted as a literal value

prepare "SELECT * FROM tablename WHERE ? = 1234" -- the parameter will
be interpreted as another literal value, not a column name.

prepare "SELECT * FROM ? WHERE columname = 1234" -- NOT LEGAL

prepare "SELECT * FROM tablename WHERE columname ? 1234" -- NOT LEGAL,
you can't parameterize operators such as '=' vs. '!='

Regards,
Bill K.

Re: Order by primary key

Bill Karwin wrote:

> Query parameters for prepared statements can be values, but not table or
> column names, and not any other syntax element. In other words:
>
> prepare "SELECT * FROM tablename WHERE columname = ?" -- LEGAL,
> parameter will be interpreted as a literal value
>
> prepare "SELECT * FROM tablename WHERE ? = 1234" -- the parameter will
> be interpreted as another literal value, not a column name.

Thanks Bill,

That makes a lot of sense. In actual fact, it would be simple for me to
move my SQL to the server and then execute it from there. However, I'm
still a little stuck on the best way to pull individual results.

The full situation is this: the table implements a cache of 'rows', and
fills up it's cache in the background while you're viewing the other
results. The cache itself is responsible for fetching any values you
request that is doesn't have (just like a regular cache), so there
could be a situation where I need to fetch (for example) rows 4, 12 and
19 on a specific prepared query, regardless of the ORDER BY within that
query.

I predict the ORDER BY will be fixed in each SQL query. I know you can
use LIMIT startindex,offset to grab a subset of results, but is it
possible to select individual rows without explicity referring to them
with a WHERE colname = x syntax?

I hope that makes sense.

Many thanks,
Matt.