SQL error with "new query()" in ColdFusion / Railo script

Question

I get a very unhelpful SQL error from Railo when trying to do an insert. I think it has something to do with the :param values I am passing to it; as when I take these out and replace them with a simple string, it works.

Here's my code (which sits inside a CFC):

local.registerUserQuery = new query();
            local.registerUserQuery.setDatasource("popwave");
            local.registerUserQuery.setSQL("

                INSERT INTO users (

                    userUUID,
                    userName,
                    userPassword,
                    userEmail,
                    userToken,
                    userCreated,
                    userBiography,
                    userCommentPoints,
                    userLinkPoints,
                    userImageID,
                    userRemoved,
                    userCategoriesOwner,
                    userCategoriesSubscribed

                )

                VALUES (

                    '#createUUID()#' , 
                    :userName , 
                    :userPassword , 
                    :userEmail , 
                    '#local.token#' , 
                    #createODBCDate(now())# , 
                    '' , 
                    0, 
                    0, 
                    0, 
                    0, 
                    0, 
                    0 

                )

            "); 

            local.registerUserQuery.setName("registerUser");

            local.registerUserQuery.addParam( name="userName", value="#arguments.userName#", cfsqltype="cf_sql_varchar" ); 
            local.registerUserQuery.addParam( name="userPassword", value="#arguments.userPassword#", cfsqltype="cf_sql_varchar" ); 
            local.registerUserQuery.addParam( name="userEmail", value="#arguments.userEmail#", cfsqltype="cf_sql_varchar" );

            local.registerUserQuery.execute();

I cannot understand for the life of me why this is throwing an error! I need to be able to use :param's.

Here's the Railo 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 '' at line 49

Examining that line number does not seem to point to anything related. This query is on something like line 200! And, as mentioned earlier...the SQL seems to work when I replace these :param values.

Is there actually something I am doing wrong here? Pulling my hair out!

Thanks, Michael.

EDIT:

Forgot to mention, the Railo version is:

Railo 3.3.1.000 Error (database)

score 4 · Accepted Answer · answered Feb 15 '12 at 18:45

4

Sounds like you are experiencing issue RAILO-1281 which should be fixed in 3.3.1.005.

"RAILO-1281: When doing a query INSERT with CFScript "new query()" and using a cfqueryparam with addParam(), the end ")" is removed"

As an aside, you should use addParam for all of the dynamic values.

answered Feb 15 '12 at 18:45

Leigh

28,765
10
55
103

Ok thanks. I've reverted to using a tag-based CFC now. I have tried using cfscript but I find it's just not that up to it yet. Oh well! Thanks for the info. – Michael Giovanni Pumo Feb 15 '12 at 18:51
Btw, aside from upgrading the comments mention a fix you can manually apply to your `query.cfc` file pretty easily. But it is up to you. – Leigh Feb 15 '12 at 18:55

score 0 · Answer 2 · answered Feb 15 '12 at 17:23

Try this

local.registerUserQuery.setSQL(reReplace("

            INSERT INTO users (

                userUUID,
                userName,
                userPassword,
                userEmail,
                userToken,
                userCreated,
                userBiography,
                userCommentPoints,
                userLinkPoints,
                userImageID,
                userRemoved,
                userCategoriesOwner,
                userCategoriesSubscribed

            )

            VALUES (

                '#createUUID()#' , 
                :userName , 
                :userPassword , 
                :userEmail , 
                '#local.token#' , 
                #createODBCDate(now())# , 
                '' , 
                0, 
                0, 
                0, 
                0, 
                0, 
                0 

            )

        ", "\t|\n", " ", "all"));

That doesn't seem to solve anything, but instead creates another error: parameter 2 of the function right must be a positive number now [0]. I've tried manually removing the spacing too; no luck. — Michael Giovanni Pumo, Feb 15 '12 at 17:42

score 0 · Answer 3 · answered Feb 15 '12 at 17:32

0

Not sure if this is your problem, but it's a good idea to use addParam for all your variables, not just the sql Injection risks.

It ensures they are passed correctly with the correct formatting and also enforces a good habit. Maybe I'm wrong about this, but doesn't #createODBCDateTime# need to be inside single quotes for mySql? addParam will do this stuff for you.

answered Feb 15 '12 at 17:32

Jonathan Rowny

7,588
1
18
26

I don't think it causes a problem, as the query worked when I simply removed all :param values. The date goes into a date/time column type, so it's not a varchar and thus exempt from the need to have quotes around it I think. – Michael Giovanni Pumo Feb 15 '12 at 17:39
@MichaelGiovanniPumo See this post: http://stackoverflow.com/questions/8551940/mysql-insert-datetime-into-other-datetime-field syntax error caused by NOT using single quotes. Also, see the official documentation. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html And another reference to requiring the single quotes: http://stackoverflow.com/questions/5452124/inserting-integer-and-datetime-from-php-to-mysql-5-0-db-error – Jonathan Rowny Feb 15 '12 at 18:43
@Jonathan - That pertains to date *strings*. `createODBCDate` returns a datetime *object* which is handled differently. Datetime objects do not need to be single quoted (in fact adding them causes a syntax error). – Leigh Feb 15 '12 at 19:04
1

@Leigh OHhh, I forgot that returned an object. My CF is rusty. – Jonathan Rowny Feb 15 '12 at 19:21

SQL error with "new query()" in ColdFusion / Railo script

3 Answers3