473,326 Members | 2,255 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,326 software developers and data experts.

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.

Jul 5 '06 #1
4 7788
be*************@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/...on-schema.html

Regards,
Bill K.
Jul 6 '06 #2
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.

Jul 6 '06 #3
be*************@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.
Jul 6 '06 #4

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.

Jul 6 '06 #5

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

Similar topics

17
by: Philip Yale | last post by:
I'm probably going to get shot down with thousands of reasons for this, but I've never really heard or read a convincing explanation, so here goes ... Clustered indexes are more efficient at...
14
by: gilles27 | last post by:
I am currently undertaking a review of the primary keys in a SQL Server 2000 database with a view to improving performance of queries. I have heard that, in the case of compound primary keys, it...
7
by: Yannick Turgeon | last post by:
Hello all, I'm using SS2K on W2k. I'v got a table say, humm, "Orders" with two fields in the PK: OrderDate and CustomerID. I would like to add an "ID" column which would be auto-increment...
12
by: Joost Kraaijeveld | last post by:
Hi all, Is it possible to create a sorted primary key in DB/2 and if so, what is the SQL script syntax for that? I need it to convert a Topspeed (Clarion) database into DB/2: they use sorted...
7
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table...
3
by: Ptbrady | last post by:
"Order By" fails in form for linked table. -------------- My A2K database has worked well for several years, but now has been split into front-end/back-end and has the following problem. I have a...
3
by: Danny | last post by:
Hi again I am trying to import from an excel spreadsheet into access 2002, but the order that is in the spreadsheet is not preserved when I import. using DoCmd.TransferSpreadsheet in code. ...
3
by: Antanas | last post by:
Why is that even though I have added index with DESC sort order on primary key, records in Control Center are displayed by the sequence they were previously inserted, not by the value of primary...
9
by: John Rivers | last post by:
Hello, if you create this table: create table hello ( int a , int b constraint pk_hello primary key clustered ( a, b ) )
2
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.