3 cfqueryparam questions

[Guest]

In a cfqueryparam tag for a SQL server text type       , is cfsqltype="CF_SQL_CHAR"          with maxlength="256" ok ?

In a cfqueryparam tag for a SQL server datetime type,is cfsqltype="CF_SQL_TIMESTAMP" with a maxlength="23" ok ?

Is maxlength needed in either of these situations ?

[-__cfSearching__-]

What do you mean by "ok"?

I suspect the code would execute, if that is what you are asking.  But specifying the length of a date/time value seems a little unusual.  What is the context of your question?

[Dan_Bracuk]

Max length is not needed for timestamps.

For the character field, it will probably prevent your app from crashing if you try to pass a 10 character string to a char (9) field.  However, it will probably just truncate your value which might not be what you want to happen.

[Owainnorth]

it will probably prevent your app from crashing if you try to pass a 10 character string to a char (9) field.

I have a feeling it won't, it'll just throw a CF exception rather than a Database exception, it just means the database connection is never attempted.

To be honest, I haven't used maxlength in years, it seems an odd (and largely pointless) way of enforcing security. As mentioned it's nonsensical on timestamps, don't bother with that.

If your site is sensibly protected against SQL injection attacks, maxlength really won't do a lot.

[Adam Cameron.]

it will probably prevent your app from crashing if you try to pass a 10 character string to a char (9) field.

I have a feeling it won't, it'll just throw a CF exception rather than a Database exception, it just means the database connection is never attempted.

Correct.

To be honest, I haven't used maxlength in years, it seems an odd (and largely pointless) way of enforcing security. As mentioned it's nonsensical on timestamps, don't bother with that.

If your site is sensibly protected against SQL injection attacks, maxlength really won't do a lot.

I guess the only benefit is that the code won't bother the DB if the data won't fit in the DB column.  However that's probably the job of the database to manage, not the job of the application code, so I'd not bother using it.

--

Adam

[Guest]

I'm new to using cfqueryparamp outside a dev area. The code runs but when looking it over, I wondered if the maxlengths were needed for what seemed to be data with fixed widths. I will take the maxlengths out.

[Dan_Bracuk]

Regarding:

I guess the only benefit is that the code won't bother the DB if the data won't fit in the DB column.  However that's probably the job of the database to manage, not the job of the application code, so I'd not bother using it.

I disagree.  Having actually tried it and getting a CF error, I see no benefit at all to the maxlength attribute.

As far as the application code is concerned, it's job includes ensuring that query strings and paramaterized values will result in a successful outcome.

[Owainnorth]

One other point - I used to use the maxlength, but it became extremely annoying when I dived into the database to change a column length only for the application to still throw exceptions due to the queryparam maxlength attribute.

Following the idea that every setting should be stored only in one place, I put validation in place if relevant and they just let the database do the work.

[Adam Cameron.]

Regarding:

I guess the only benefit is that the code won't bother the DB if the data won't fit in the DB column.  However that's probably the job of the database to manage, not the job of the application code, so I'd not bother using it.

I disagree.  Having actually tried it and getting a CF error, I see no benefit at all to the maxlength attribute.

Um... so that's not disagreeing with me then, is it?

As far as the application code is concerned, it's job includes ensuring that query strings and paramaterized values will result in a successful outcome.

Indeed.  However what difference do you perceive there being in the app erroring if the value is too long compared to the DB erroring if the value is too long?

Doing on the app side of things just adds unnecessary maintenance overhead (as per Owain's most recent comment), as far as I can tell.

--

Adam