search

opensourceautomation / Open-Source-Automation

Open Source Automation is a home and commercial automation engine
www.opensourceautomation.com
GNU Lesser General Public License v3.0
97 stars 62 forks source link

Using single quote in object name results in SQL error #298

Closed mlinnen closed 8 years ago

mlinnen commented 8 years ago

In using the Bluetooth plugin if you pair a device and change the name of the device and add a single quote in the name you get a SQL error when the status of the bluetooth thing is attempted to be updated.

B0358D17AA34 - Mike's phone failed with Connect attempt. exception: API - GetObjectPropertyValue error: 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 'phone') OR UPPER(object_alias) = UPPER('Mike's phone')) AND property_name='Disco' at line 1

This might be an issue with all objects but I only tried it with bluetooth discovered things.

VaughnRupp commented 8 years ago

I see code all over the place that uses SQL without parameter, ie: runSQL = "SELECT * FROM SomeWhere WHERE Name='" + _name + "'"; Code like this will not handle a single quote without escaping the single quote.

Many places have previously been tested against ', but new code all the time skips the escaping, and introduces breaks.

I will test it and see what I find and do some general clean up.

VaughnRupp commented 8 years ago

I found the issue I described in ObjectManager.GetPropertyValue, which is a very common call. This cleared up the errors I was able to reproduce in the Jabber plugin.

VaughnRupp commented 8 years ago

I finally found a BT adapter and tested it and it works fine now.