insert device using sql - incorrect name validation

stephan.steiner · ‎10-27-2017

Given that I've been unable to somehow change the name my created device templates (see other threads), I've figured I'd give a direct SQL insert a go.

So I created a simple SQL query to generate a TCT template. The idea then being to update the device with AXL later (copying all the values from something that's already there). So here's my insert SQL

insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCTMTRATEMPLATE2', 253, 562, 449, 11, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

That returns

The specified name has invalid characters or is not formatted correctly for this device type.Valid characters include [a-zA-Z0-9._-]{1,50}.

and obviously that's incorrect. I mean.. the name of the insert should easily satisfy the regex. But it seems to be the same regex that is being returned when you try to change the name later one (be it with AXL or an SQL update). And I wonder how on earth this does not trip up ccmadmin. Shouldn't the DBL perform the same kind of data validation regardless of where the data originates?

AXL log is as follows

2017-10-27 14:42:16,492 DEBUG [http-bio-443-exec-127] filters.TimingFilter - Received request 1505911709320 from sdkuser at IP 10.90.28.61

2017-10-27 14:42:16,492 DEBUG [http-bio-443-exec-127] wrappers.RequestHeaderWrapper - Inside Request Header Wrapper

2017-10-27 14:42:16,493 DEBUG [http-bio-443-exec-127] filters.AuthenticationFilter - Operation:executeSQLUpdate api:executeSQLUpdate

2017-10-27 14:42:16,513 DEBUG [http-bio-443-exec-127] filters.ThrottlingFilter - DBLCNQueue Count: 0

2017-10-27 14:42:16,513 DEBUG [http-bio-443-exec-127] filters.ThrottlingFilter - Successfully set the value of counter: 4 value: 0

2017-10-27 14:42:16,514 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - Checking request version [11.5]

2017-10-27 14:42:16,514 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - Checking if requested api [executeSQLUpdate] the implementedHandlers list

2017-10-27 14:42:16,514 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - [executeSQLUpdate] is not in the implementedHandlers list

2017-10-27 14:42:16,515 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - Checking if version is 8.x

2017-10-27 14:42:16,515 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - function to check if the version is 8.x

2017-10-27 14:42:16,516 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - version is not 8.x

2017-10-27 14:42:16,516 INFO [http-bio-443-exec-127] servletRouters.AXLAlpha - Executing api: executeSQLUpdate in axis

2017-10-27 14:42:16,516 DEBUG [http-bio-443-exec-127] wrappers.RequestNamespaceWrapper - Inside Request Wrapper

2017-10-27 14:42:16,517 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - AXL REQUEST :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5"><soapenv:Header/><soapenv:Body><ns:executeSQLUpdate sequence="0"> <sql>insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCTMTRATEMPLATE2', 253, 562, 449, 11, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')</sql></ns:executeSQLUpdate></soapenv:Body></soapenv:Envelope>

2017-10-27 14:42:16,518 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - Request processed by AXIS

2017-10-27 14:42:16,520 DEBUG [http-bio-443-exec-127] axlapiservice.Handler - dbConnector Initialization in handler.java

2017-10-27 14:42:16,521 DEBUG [http-bio-443-exec-127] axlapiservice.Axl - Connection given to current thread

2017-10-27 14:42:16,521 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - In begin transaction of AXLCallflow and created pub connector

2017-10-27 14:42:16,521 DEBUG [http-bio-443-exec-127] axlapiservice.AXLAPIServiceSkeleton - In ExecuteSQLQueryResponseDocument

2017-10-27 14:42:16,522 DEBUG [http-bio-443-exec-127] axlapiservice.ExecuteSqlHandler - insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCTMTRATEMPLATE2', 253, 562, 449, 11, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

2017-10-27 14:42:16,522 DEBUG [http-bio-443-exec-127] axlapiservice.ExecuteSqlHandler - sqlQuery:insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('tctmtratemplate2', 253, 562, 449, 11, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

2017-10-27 14:42:16,522 DEBUG [http-bio-443-exec-127] axlapiservice.ExecuteSqlHandler - userRankTable:

2017-10-27 14:42:16,658 WARN [http-bio-443-exec-127] axlapiservice.ExecuteSqlHandler - java.sql.SQLException: 491

2017-10-27 14:42:16,658 ERROR [http-bio-443-exec-127] axlapiservice.AXLAPIServiceSkeleton - SQL EXCEPTION has occurred, will be sending you the exact error Info

2017-10-27 14:42:16,659 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - In rollback transaction and created pub connector

2017-10-27 14:42:16,659 DEBUG [http-bio-443-exec-127] axlapiservice.Axl - Connection closed and hashmap entry removed in AXL.java closing connection

2017-10-27 14:42:16,659 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - Rollback transaction connector object closed

2017-10-27 14:42:16,660 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - In AXL CAll Flow class, method getSOAPError

2017-10-27 14:42:16,660 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - checking typeDBErrors for sql exception name

2017-10-27 14:42:16,660 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - Setting soap fault

2017-10-27 14:42:16,661 DEBUG [http-bio-443-exec-127] axlapiservice.AXLCallFlow - Setting soap fault2

2017-10-27 14:42:16,664 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - <?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><soapenv:Fault><faultcode>soapenv:Client</faultcode><faultstring>The specified name has invalid characters or is not formatted correctly for this device type.Valid characters include [a-zA-Z0-9._-]{1,50}</faultstring><detail><axlError><axlcode>491</axlcode><axlmessage>The specified name has invalid characters or is not formatted correctly for this device type.Valid characters include [a-zA-Z0-9._-]{1,50}</axlmessage><request>executeSQLUpdate</request></axlError></detail></soapenv:Fault></soapenv:Body></soapenv:Envelope>

2017-10-27 14:42:16,664 DEBUG [http-bio-443-exec-127] servletRouters.AXLAlpha - Finished processing request

2017-10-27 14:42:16,665 INFO [http-bio-443-exec-127] filters.TimingFilter - Request 1505911709320 received from sdkuser at ip 10.90.28.61 was processed in 173ms

stephan.steiner · ‎10-27-2017

So I finally figured out how to bypass the incorrect validation. But, more validation in the DBL that is erroneous. Inserting a TCT like this

insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprofile, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCT MTRATEMPLATE2', 253, 562, 449, 0, 4, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

yields

Security Profile is not supported for this device-Security Profile is not supported for this device-430

Ahem... when I look at my Jabber for iPhone templates, I see that they do have the same security profile assigned as I'm trying to assign. So, I just wonder how ccmadmin manages to insert records that not only contain a value, but the very specific value I'm trying to insert.

AXL logs:

2017-10-27 17:32:21,004 DEBUG [http-bio-443-exec-212] filters.TimingFilter - Received request 1505911709604 from sdkuser at IP 10.90.28.61

2017-10-27 17:32:21,004 DEBUG [http-bio-443-exec-212] wrappers.RequestHeaderWrapper - Inside Request Header Wrapper

2017-10-27 17:32:21,004 DEBUG [http-bio-443-exec-212] filters.AuthenticationFilter - Operation:executeSQLUpdate api:executeSQLUpdate

2017-10-27 17:32:21,024 DEBUG [http-bio-443-exec-212] filters.ThrottlingFilter - DBLCNQueue Count: 0

2017-10-27 17:32:21,024 DEBUG [http-bio-443-exec-212] filters.ThrottlingFilter - Successfully set the value of counter: 4 value: 0

2017-10-27 17:32:21,024 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - Checking request version [11.5]

2017-10-27 17:32:21,025 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - Checking if requested api [executeSQLUpdate] the implementedHandlers list

2017-10-27 17:32:21,025 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - [executeSQLUpdate] is not in the implementedHandlers list

2017-10-27 17:32:21,025 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - Checking if version is 8.x

2017-10-27 17:32:21,025 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - function to check if the version is 8.x

2017-10-27 17:32:21,025 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - version is not 8.x

2017-10-27 17:32:21,025 INFO [http-bio-443-exec-212] servletRouters.AXLAlpha - Executing api: executeSQLUpdate in axis

2017-10-27 17:32:21,026 DEBUG [http-bio-443-exec-212] wrappers.RequestNamespaceWrapper - Inside Request Wrapper

2017-10-27 17:32:21,026 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - AXL REQUEST :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5"><soapenv:Header/><soapenv:Body><ns:executeSQLUpdate sequence="0"> <sql>insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprofile, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCT MTRATEMPLATE2', 253, 562, 449, 0, 4, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')</sql></ns:executeSQLUpdate></soapenv:Body></soapenv:Envelope>

2017-10-27 17:32:21,026 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - Request processed by AXIS

2017-10-27 17:32:21,028 DEBUG [http-bio-443-exec-212] axlapiservice.Handler - dbConnector Initialization in handler.java

2017-10-27 17:32:21,029 DEBUG [http-bio-443-exec-212] axlapiservice.Axl - Connection given to current thread

2017-10-27 17:32:21,029 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - In begin transaction of AXLCallflow and created pub connector

2017-10-27 17:32:21,029 DEBUG [http-bio-443-exec-212] axlapiservice.AXLAPIServiceSkeleton - In ExecuteSQLQueryResponseDocument

2017-10-27 17:32:21,030 DEBUG [http-bio-443-exec-212] axlapiservice.ExecuteSqlHandler - insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprofile, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('TCT MTRATEMPLATE2', 253, 562, 449, 0, 4, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

2017-10-27 17:32:21,030 DEBUG [http-bio-443-exec-212] axlapiservice.ExecuteSqlHandler - sqlQuery:insert into device(name, tkclass, tkmodel, tkproduct, tkdeviceprofile, tkdeviceprotocol, fkphonetemplate, fksecurityprofile) values('tct mtratemplate2', 253, 562, 449, 0, 4, 'cfd9c6fb-b9fa-4a2b-935c-fc5e8a859f5d', '5cd3a1a0-98b9-4a97-acb0-b7a50e5c5076')

2017-10-27 17:32:21,030 DEBUG [http-bio-443-exec-212] axlapiservice.ExecuteSqlHandler - userRankTable:

2017-10-27 17:32:21,119 WARN [http-bio-443-exec-212] axlapiservice.ExecuteSqlHandler - java.sql.SQLException: 430

2017-10-27 17:32:21,120 ERROR [http-bio-443-exec-212] axlapiservice.AXLAPIServiceSkeleton - SQL EXCEPTION has occurred, will be sending you the exact error Info

2017-10-27 17:32:21,120 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - In rollback transaction and created pub connector

2017-10-27 17:32:21,121 DEBUG [http-bio-443-exec-212] axlapiservice.Axl - Connection closed and hashmap entry removed in AXL.java closing connection

2017-10-27 17:32:21,121 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - Rollback transaction connector object closed

2017-10-27 17:32:21,121 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - In AXL CAll Flow class, method getSOAPError

2017-10-27 17:32:21,122 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - checking typeDBErrors for sql exception name

2017-10-27 17:32:21,122 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - Setting soap fault

2017-10-27 17:32:21,122 DEBUG [http-bio-443-exec-212] axlapiservice.AXLCallFlow - Setting soap fault2

2017-10-27 17:32:21,126 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - <?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><soapenv:Fault><faultcode>soapenv:Client</faultcode><faultstring>Security Profile is not supported for this device</faultstring><detail><axlError><axlcode>430</axlcode><axlmessage>Security Profile is not supported for this device</axlmessage><request>executeSQLUpdate</request></axlError></detail></soapenv:Fault></soapenv:Body></soapenv:Envelope>

2017-10-27 17:32:21,126 DEBUG [http-bio-443-exec-212] servletRouters.AXLAlpha - Finished processing request

2017-10-27 17:32:21,126 INFO [http-bio-443-exec-212] filters.TimingFilter - Request 1505911709604 received from sdkuser at ip 10.90.28.61 was processed in 122ms