Ampersand in LDAP Company Name

[AnthonySadler]

Hey all:

We have OTRS checking against Active Directory for customers and agents. Customer Companies are pulled from the Company field in Active Directory and set against a company in OTRS.

When we go into the customer.pl interface, we see the "my tickets" and "company tickets" tab as normal. When going into company tickets however, nothing shows up. That is because this is being thrown in apache's error.log:

Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(st.customer_id = 'R Brands') )) LIMIT 10000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id NOT IN ( 1, 4, 6, 7, 8 ) AND (LOWER(st.customer_id) IN (LOWER('Y&R Brands')) OR st.customer_user_id = 'blargh') AND sq.group_id IN (1, 2, 3, 4, 5, 6) AND (((st.customer_id = 'Y') (st.customer_id = 'R Brands') )) LIMIT 10000'

I think the ampersand we have in the company field in AD is breaking it. If I remove the ampersand, all goes well.

Any way to escape this character?

Thanks

Anthony

[crythias]

You've found what can be a serious bug. Please report it because it enables an SQL injection.
http://bytes.com/serversidescripting/pe ... page3.html
http://bugs.otrs.org

[AnthonySadler]

Hey:

Thanks for that. Bug filed: http://bugs.otrs.org/show_bug.cgi?id=7216. Let me know if you need any more details.

Anthony

[ferrosti]

I´d rather say there is a missing operator in between the middle braces here:
AND (((st.customer_id = 'Y') ??? (st.customer_id = 'R Brands') ))

For me it looks like the ampersand is escaped correctly, but the OTRS query itself brakes up the query.

Comments appreciated

[AnthonySadler]

I'm far from an expert on MySQL and could be talking complete nonsense, but I'm not sure I'd agree with that one. While I can see your point, the problem is fixed as soon as I remove the & from the company name.

Unless the addition of the & creates another bit to add in on the SQL query and that is missing the operator. That could explain both points?

[ferrosti]

What I meant to say is that mySQL itself handles the ampersand correctly, as you can see in

Code: Select all

IN (LOWER('Y&R Brands'))

The mySQL error complains about the syntax near (before)

Code: Select all

(st.customer_id = 'R Brands') )) LIMIT 10000'

Having a closer look at the syntax before this point is

Code: Select all

(st.customer_id = 'Y') 

These parts together you´ll get

Code: Select all

AND (((st.customer_id = 'Y') (st.customer_id = 'R Brands') ))

And this part shows that there were an operator like AND, OR, NOT missing if this were what you wanted to select. The SQL itself then won´t make any sense, since both parts would select for either 'Y' or 'R Brands' in your customer_id and exactly these strings, which is not 'true' in either case.
My conclusion is that OTRS breaks the SQL syntax before sending it to mySQL.

[crythias]

ferrosti is correct. There is probably code parsing before the entry that separates based upon the & existing. I was wrong about this being MySQL/SQL injection.