UPDATE as subquery of SELECT

#11

On 3/25/2011 2:05 PM, Alfio Emanuele Fresta wrote:

Quote:

On 25 Mar, 16:09, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
In article<9b81d0a5-450b-48a6-a3d4-24d2119c0... (AT) h38g2000yqn (DOT) googlegroups.com>, Alfio Emanuele Fresta<alfio.emanuel... (AT) gmail (DOT) com> wrote:

On 25 Mar, 10:33, strawberry<zac.ca... (AT) gmail (DOT) com> wrote:
Demonstration of your efforts at constructing the query for yourself are =
appreciated. However, if you want help I suggest you provide a representati=
ve data set together with an illustration (in something approaching plain E=
nglish) of what it is you're trying to achieve.

The representative data set doesn't matter, I guess.
All I need is to execute an UPDATE query via an SQL-INJECTION, like
"UPDATE core_user SET pass='1' WHERE userid='/admin'"
In can only inject SQL in the WHERE clause of a SELECT query.
I don't mind about what will the SELECT return.
I cannot use multiple queries.

So in other words, you want to hack someone else's site but don't have the
first clue how to do it yourself -- and you want us to help you.

I'm not looking for anything like this, I'm not at all.
I'm neither trying to hack anyone else's site, just wanna learn.
It's not easy to explain it in english, because I'm not a native
english speaker.

What I wonder about is:
May a malicious user get its malicious query to be executed, something
like:

SELECT title FROM news WHERE id = '123' AND
(UPDATE user SET level=100 WHERE id='44')> 0
AND status='1'

... using at its advantage some bad-programmed and not-escaped code
looking like this:

$query = "SELECT title FROM news WHERE id = '".$_GET['id']."'";

The malicious query written above actually doesn't work.
I think there's no reason an application would use an UPDATE query
as subquery of a SELECT one, but it doesn't mean it
would not be executed from the database server.

In fact, the MySQL documentation says
- WHERE: it also accepts subqueries as conditions
- SUBQUERY: "One restriction is that a subquery's statement must be
one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO."
(Source: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html)

Actually, there are keywords like "IN" that accepts subqueries (even
if "IN" only accepts SELECT ones).

Listening to the same documentation, an UPDATE query RETURNS the
number of affected rows.

So, shouldn't something like this:
SELECT title FROM news WHERE id = '123' AND
(UPDATE user SET level=100 WHERE id='44')> 0
AND status='1'

Be interpreted such as:
SELECT title FROM news WHERE id = '123' AND
1> 0
AND status='1'

SELECT title FROM news WHERE id = '123' AND
true
AND status='1'

That actually works?

Finally, I'm wondering if the user could manipulate some SELECT query
injecting malicious SQL in order to
UPDATE, DELETE, DROP or INSERT (to write).
Could it ever succeed to modify the database's data?

PS.: The php mysql_query() function I'm assuming
the bad-coded application uses,
actually forbids multiple queries ";"-separated.

FOAD.

Have a nice day you too

It's not a problem if you properly validate your data - which you should
ALWAYS do. Even if it were to fail now, who knows what might be added
in a future release?

The bottom line - ALWAYS validate data from the user - NO MATTER WHAT IT
IS. This is true not only for SQL injection, but as a general coding
practice.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#12

Quote:

SQL doesn't allow an UPDATE as a subquery of a SELECT statement.

Actually ANSI SQL does allow that:
SELECT * FROM OLD TABLE(UPDATE T SET c1 = 5)

What mySQL allows is another matter..

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

#13

On 3/27/2011 4:22 PM, Serge Rielau wrote:

Quote:

SQL doesn't allow an UPDATE as a subquery of a SELECT statement.

Actually ANSI SQL does allow that:
SELECT * FROM OLD TABLE(UPDATE T SET c1 = 5)

What mySQL allows is another matter..

Cheers
Serge

Interesting, Serge. I haven't seen that in the SQL specs. Do you know
which version specified it?

Thanks for the info.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#14

Quote:

Interesting, Serge. I haven't seen that in the SQL specs. Do you know
which version specified it?
Should be in SQL:2008

Checking....

We first published the extension at VLDB 2004:
http://portal.acm.org/citation.cfm?id=1316774

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

#15

On 3/28/2011 7:58 AM, Serge Rielau wrote:

Quote:

Interesting, Serge. I haven't seen that in the SQL specs. Do you know
which version specified it?
Should be in SQL:2008
Checking....

We first published the extension at VLDB 2004:
http://portal.acm.org/citation.cfm?id=1316774

Cheers
Serge

Interesting, and I can see where there could be performance
improvements. But which version of the ANSI standard picked it up?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#16

On 3/28/2011 9:50 AM, Jerry Stuckle wrote:

Quote:

On 3/28/2011 7:58 AM, Serge Rielau wrote:
Interesting, Serge. I haven't seen that in the SQL specs. Do you know
which version specified it?
Should be in SQL:2008
Checking....

We first published the extension at VLDB 2004:
http://portal.acm.org/citation.cfm?id=1316774

Cheers
Serge

Interesting, and I can see where there could be performance
improvements. But which version of the ANSI standard picked it up?

I have bugged our standards reps and here is the feedback I got:

"Yes, SELECT FROM INSERT has indeed made it into ANSI as well as ISO SQL
standards, but it is not yet visible to the wider world since it was
introduced after the current version of SQL standard, SQL-2008, came
out. It will become visible when the next version gets published, which
is expected either late this year or early next year. It normally takes
3 to 4 year cycle for a new version of SQL standard to come out."

If you are interested I can send you a PDF of the submission.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

#17

On 4/4/2011 6:59 PM, Serge Rielau wrote:

Quote:

On 3/28/2011 9:50 AM, Jerry Stuckle wrote:
On 3/28/2011 7:58 AM, Serge Rielau wrote:
Interesting, Serge. I haven't seen that in the SQL specs. Do you know
which version specified it?
Should be in SQL:2008
Checking....

We first published the extension at VLDB 2004:
http://portal.acm.org/citation.cfm?id=1316774

Cheers
Serge

Interesting, and I can see where there could be performance
improvements. But which version of the ANSI standard picked it up?

I have bugged our standards reps and here is the feedback I got:

"Yes, SELECT FROM INSERT has indeed made it into ANSI as well as ISO SQL
standards, but it is not yet visible to the wider world since it was
introduced after the current version of SQL standard, SQL-2008, came
out. It will become visible when the next version gets published, which
is expected either late this year or early next year. It normally takes
3 to 4 year cycle for a new version of SQL standard to come out."

If you are interested I can send you a PDF of the submission.

Cheers
Serge

No, that's fine, Serge. And that makes sense - I didn't think it was in
the current standard.

I appreciate the info!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

UPDATE as subquery of SELECT

comp.databases.mysql comp.databases.mysql

Re: UPDATE as subquery of SELECT - 03-25-2011 , 01:35 PM

Re: UPDATE as subquery of SELECT - 03-27-2011 , 03:22 PM

Re: UPDATE as subquery of SELECT - 03-27-2011 , 09:00 PM

Re: UPDATE as subquery of SELECT - 03-28-2011 , 06:58 AM

Re: UPDATE as subquery of SELECT - 03-28-2011 , 08:50 AM

Re: UPDATE as subquery of SELECT - 04-04-2011 , 05:59 PM

Re: UPDATE as subquery of SELECT - 04-04-2011 , 06:59 PM