How to LIMIT results in html php/sql query?

[emily_g107@hotmail.com]

Hi,

I need to limit results in the following query type:

http://www.somewhere.com/php/sql-a.php3?server=1&db=mydatabase&table=mytable&sql_query=SELECT+Field_1%2CField_2%2CField_3%2Cidno+from+mytable+where+1+and+field_1+like+%22string%22+&sql_order=&pos=1

I found a reference that says I should be able to use LIMIT x[,y], but
I don't know where/exactly how to add that to the string. Once I know
what it's supposed to look like, and can write something to generate
it.

If someone could post an example using the above and limiting the
output to 100 records starting at position 1, that would be great.

[Carl]

Re: How to LIMIT results in html php/sql query?

emily_g107@hotm ail.com wrote:[color=blue]
> Hi,
>
> I need to limit results in the following query type:
>
> http://www.somewhere.com/php/sql-a.p...l_order=&pos=1
>
> I found a reference that says I should be able to use LIMIT x[,y], but
> I don't know where/exactly how to add that to the string. Once I know
> what it's supposed to look like, and can write something to generate
> it.
>
> If someone could post an example using the above and limiting the
> output to 100 records starting at position 1, that would be great.
>[/color]

It really depends on the database you're using.
In mysql, adding " LIMIT 5" to the end of your sql statement will return
5 rows, adding "LIMIT 10, 20" will return 20 rows starting with row
number 10.

It should also be noted that passing the database query in the URL like
your example shows is a _really_bad_ idea if you expect to allow other
people to use this page, and just a bad idea otherwise.

What would prevent some malicious user from manually changing the url to
try and execute the statement "DELETE+ALL+FRO M+super_importa nt_table",
OR WORSE!

Carl.

[emily_g107@hotmail.com]

Re: How to LIMIT results in html php/sql query?

Carl wrote:
[color=blue]
> emily_g107@hotm ail.com wrote:
>[color=green]
>> Hi,
>>
>> I need to limit results in the following query type:
>>
>> http://www.somewhere.com/php/sql-a.p...l_order=&pos=1
>>
>>
>> I found a reference that says I should be able to use LIMIT x[,y], but
>> I don't know where/exactly how to add that to the string. Once I know
>> what it's supposed to look like, and can write something to generate
>> it.
>>
>> If someone could post an example using the above and limiting the
>> output to 100 records starting at position 1, that would be great.
>>[/color]
>
> It really depends on the database you're using.
> In mysql, adding " LIMIT 5" to the end of your sql statement will return
> 5 rows, adding "LIMIT 10, 20" will return 20 rows starting with row
> number 10.
>
> It should also be noted that passing the database query in the URL like
> your example shows is a _really_bad_ idea if you expect to allow other
> people to use this page, and just a bad idea otherwise.
>
> What would prevent some malicious user from manually changing the url to
> try and execute the statement "DELETE+ALL+FRO M+super_importa nt_table",
> OR WORSE!
>
> Carl.[/color]

The database is read-only, but thanks for the warning.

How exactly do I add "LIMIT 10, 20" to the html string? I tried some
different ways and either got error messages (generic) or it had no
effect.

This is using MySQL, and I know LIMIT is supported.

For example: I tried adding
+LIMIT+1%2C+100
to the end, or before the
+&sql_order=&po s=1
I Also tried adding
&LIMIT+1%2C+ 100
(no "+")

I think maybe the &sql_order at the end needs rewritten using LIMIT.

I need the specific syntax, "+", "&", "%2C", etc. to add (say) LIMIT
10,20 to this:

http://www.somewhere.com/php/sql-a.php3?server=1&db=mydatabase&table=mytable&sql_query=SELECT+Field_1%2CField_2%2CField_3%2Cidno+from+mytable+where+1+and+field_1+like+%22string%22+&sql_order=&pos=1

Thanks

[Bill Karwin]

Re: How to LIMIT results in html php/sql query?

emily_g107@hotm ail.com wrote:[color=blue]
> How exactly do I add "LIMIT 10, 20" to the html string? I tried some
> different ways and either got error messages (generic) or it had no
> effect.
> I think maybe the &sql_order at the end needs rewritten using LIMIT.[/color]

LIMIT must come after ORDER BY in a SQL query.
See http://dev.mysql.com/doc/mysql/en/select.html

But obviously, the PHP code is concatenating the sql_order after the
sql_query in some manner. We don't know what it's doing. We don't even
know from your example what the legal values for sql_order are. Are
they field names by which to sort? We don't know how it interprets the
blank string, as is being passed here.

For instance, it _might_ be concatenating the values of sql_query and
sql_order like this:

$sql = $sql_query
. "ORDER BY "
. ($sql_order ? $sql_order : "DefaultFie ld")
. " ASCENDING"

So even if you were to pass "DefaultFie ld LIMIT 10,20" for the sql_order
parameter, you'd end up with a syntax error in the resulting SQL,
because "ASCENDING" appears out of place. And this is assuming
sql_order is there to name the field by which to sort. It could have
some totally different meaning within the PHP code.

Regards,
Bill K.

[Karl A. Krueger]

Re: How to LIMIT results in html php/sql query?

In comp.databases emily_g107@hotm ail.com wrote:[color=blue]
> I need to limit results in the following query type:
>
> http://www.somewhere.com/php/sql-a.p...l_order=&pos=1[/color]

What you seem to be doing here is very dangerous. Suppose someone saw
that URL and rewrote it as follows:

http://www.somewhere.com/php/sql-a.php3?server=1&db=mydatabase&table=mytable&sql_query=DELETE+FROM+mytable

Or even:

http://www.somewhere.com/php/sql-a.php3?server=1&db=mydatabase&table=mytable&sql_query=DROP+mytable

This is called an "SQL injection" vulnerability -- where your
application allows the user to enter arbitrary SQL statements. These
can yield all sorts of undesired results:

* public accessibility of private information
* destruction of information (as above)
* crashing of your database server (by writing a query that
takes massive computational resources to compute)
* corruption of information, possibly with substantial financial
consequences

As an example of the last, imagine that your database is serving an
online store application, and includes the price list. If the user can
enter an arbitrary query (and the store application has the access
privileges to do so -- another error, perhaps) then the user could alter
prices and then place orders for really cheap goods. :)

--
Karl A. Krueger <kkrueger@examp le.edu> { s/example/whoi/ }