Unable to INSERT INTO agains mySQL 5.5.56

dev
1

Hi all

I have a problem executing an insert into against my MySQL version 5.5.56.

<cfquery name="AddLogs" datasource="#db_datasource#" username="#db_username#" password="#db_password#">
INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dttm) 
VALUES ('#UsrID#', '#cgi.remote_addr#', '#cgi.remote_host#', '#s_ApplicationName#: Login (Usr##: #UsrID#)', '#LSDateFormat(Now(), "yyyy.mm.dd")# #TimeFormat(now(),'HH:mm:ss')#')
</cfquery>

This is the plain query:

INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dttm)
VALUES (‘10000000’, ‘10.0.0.123’, ‘10.0.0.123’, ‘thuPortal: Login (Usr#: 10000000)’, ‘2018.04.06 22:17:47’)

I get this error, no additional information within DEBUG log-level:

– exception.log –

“ERROR”,“ajp-nio-8009-exec-5”,“04/06/2018”,“22:17:47”,“”,"query tag;lucee.runtime.exp.DatabaseException
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘>
INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dt’ at line1;
lucee.runtime.exp.DatabaseException:
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:7
(…)

– error page via http –

Lucee 5.2.6.60 Error (database)
Message
Detail You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘>
INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dt’ at line 1
SQL >
INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dttm)
VALUES (‘10000000’, ‘10.0.0.123’, ‘10.0.0.123’, ‘thuPortal: Login (Usr#: 10000000)’, ‘2018.04.06 22:17:47.0’)
DatabaseName MySQL
DatabaseVersion 5.5.56-MariaDB
DriverName MySQL Connector Java
DriverVersion mysql-connector-java-8.0.9-rc ( Revision: 740a66923ca31f71558f3e1559a237e59353d32e )

I can run the query in mySQL directly and it works smooth. I tried JDBC driver 5.1.40, 6.0.6 and 8.0.9. Lucee Version is 5.2.6.60. I also tried to build the query in this style:

<cfquery name="AddLogs" datasource="#db_datasource#" username="#db_username#" password="#db_password#">
INSERT INTO thuLogs VALUES ('#UsrID#', '#cgi.remote_addr#', '#cgi.remote_host#', '#s_ApplicationName#: Login (Usr##: #UsrID#)', '#LSDateFormat(Now(), "yyyy.mm.dd")# #TimeFormat(now(),'HH:mm:ss.0')#')
</cfquery>

I’m not able to make it work. Does anybody having a hint for me?

Update: Interesting… when i add the same query to a new page that only contains this query, then it’s working fine, but unfortanetly this is not a solution for me.

<cfset UsrID = '10000000'>
<cfquery name="AddLogs" datasource="#db_datasource#" username="#db_username#" password="#db_password#">
INSERT INTO thuLogs (UsrID, remote_ipaddress, remote_fqdn, action, dttm)
VALUES ('#UsrID#', '#cgi.remote_addr#', '#cgi.remote_host#', '#s_ApplicationName#: Login (Usr##: #UsrID#)', '#LSDateFormat(Now(), "yyyy.mm.dd")# #TimeFormat(now(),'HH:mm:ss')#');
</cfquery>

Best regards
Tom

2

use cfqueryparam.

3

Using tags makes me feel icky, but here ya go…

<cfquery name="AddLogs" datasource="#db_datasource#" username="#db_username#" password="#db_password#">
INSERT INTO thuLogs (
	UsrID, 
	remote_ipaddress, 
	remote_fqdn, 
	action, 
	dttm
) VALUES (
	<cfqueryparam value="#UsrID#" cfsqltype="cf_sql_integer">,
	<cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar">,
	<cfqueryparam value="#cgi.remote_host#" cfsqltype="cf_sql_varchar">,
	<cfqueryparam value="#s_ApplicationName#: Login (Usr##: #UsrID#)" cfsqltype="cf_sql_varchar">,
	<cfqueryparam value="#LSDateTimeFormat( now(), 'yyyy.mm.dd HH:mm:ss' )#" cfsqltype="cf_sql_timestamp">
)
</cfquery>

As @Zackster suggested, use <cfqueryparam> (cfqueryparam Code Examples and CFML Documentation) to wrap your values. The list of benefits in using it include (but are not limited to):

  1. Improved security against SQL injection
  2. Improved compatibility with various DB engines and syntax
  3. Creates an execution plan in most databases (improves subsequent response times - i.e. better performance)

HTH

– Denny

4

@ddspringle @Zackster

Thanks for your feedback and thank you for the details about cfqueryparam, i will use it from now on.

I need to say, it was late yesterday and i did not see for hours that my first line ended with two > signs. Obviously i did not see it as well when i’ve copy/pasted the code for this topic.

laughing allowed

Have a nice weekend!
Tom

1 Like