So my real issue was that I wanted to super dynamically assemble the SQL and
then either execute it or do a variety of other things with it ( which I
won't go into ).
If you try to use cfqueryparam outside of a containing cfquery, ColdFusion
breaks.
So,
cfsavecontent
...cfqueryparam...
/cfsa
Anyone remember this article?
http://coldfusion.sys-con.com/node/45569
Lets you drop down into Java and pull out the field types that your db
reports back to the JDBC driver.
Its neat on the surface, but too expensive on resources to use on the
fly. Plus you have to put the for-real username a
Sounds interesting :). Give all the googlers a bone and drop us a link
or an explanation...
2009/4/16 David McGuigan :
>
> Nevermind, you can totally hack cfquery! Woot. Thanks for all the help.
>
~|
Adobe® ColdFusion® 8 softwar
> If so, does that mean my only option is writing a custom tag to simulate
> cfquery and having it handle all of that stuff the same way cfquery does? (
> Retrieve the datasource info from the adminapi, create and manage the
> connection, call all of these execution methods and then translate thei
Nevermind, you can totally hack cfquery! Woot. Thanks for all the help.
On Thu, Apr 16, 2009 at 1:59 PM, David McGuigan wrote:
> Ah, thanks guys! What I didn't realize is that you can use unnamed /
> unstored prepared statements through the driver. That makes so much more
> sense now. So ColdFus
Ah, thanks guys! What I didn't realize is that you can use unnamed /
unstored prepared statements through the driver. That makes so much more
sense now. So ColdFusion uses dynamically "prepared statements" which are
implemented in the JDBC driver, which is a totally different ballgame from
using t
I can't speak for MySQL, but in MSSQL, every query (not just prepared
statements) is processed into a Query Plan before processing. The server then
caches as many of these QPs as possible, so that repeated calls to the same
'query definition' do not have the overhead of having to re-generate t
> So if cfqueryparam is using prepared statements (which my guess would be
> that it's not), cfqueryparamming all of your variable values would improve
> performance because the statement itself isn't being re-sent to the RDMS on
> each subsequent query, only the values. But if it's not, it could
> Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer
> I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER.
> Which leads me to believe it is being scanned/validated before being sent to
> MySQL, and also makes me wonder whether cfqueryparam even uses prepared
Everything but your guess is correct ;)
Dominic
2009/4/16 David McGuigan :
>
> Well from what I read today it seems like the performance is improved for
> reused prepared statements, which may not be how cfqueryparam is
> implemented.
> Prepared statements are stored and reused by name, passing
Well from what I read today it seems like the performance is improved for
reused prepared statements, which may not be how cfqueryparam is
implemented.
Prepared statements are stored and reused by name, passing in the values for
the parameters.
So if cfqueryparam is using prepared statements (whi
Roger that re execution plans Jason, that makes perfect sense.
Dominc
2009/4/16 Jason Fisher :
> LECT fu
> FROM bar
> WHERE barId =
> AND live =
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release
Dominic, you are right that there are exceptions from a performance
perspective ... can't remember who blogged about that in detail? See Simon
Horwith's for one example:
http://www.horwith.com/index.cfm/2009/4/5/some-cf-best-practices-that-break
But you are right about the Query Plan gettin
Yes he is sure. And he is correct. With a prepared statement, an array
of values is sent to the db along with a query string that looks like
"SELECT * FROM fu WHERE bar = ?". In preparing the statement, each
value has to be added to the array using a type specific method (in
java). Therefore CF is
Just a little thing to add here is that I believe you should
parametize all values in your query, whether user generated or
constant values or whatever. This is not for security but performance.
So:
SELECT fu
FROM bar
WHERE barId =
AND live = 1
Should be:
SELECT fu
FROM bar
WHERE barId =
AND
:::It builds a prepared statement. It doesn't scan or filter anything.
You could build a prepared statement yourself.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer
I get the error: Invalid data etc for C
Ah, yes, Francois, you are correct. I forgot to mention that in addition
to creating the @var parameters (which you can see in the CF debug output),
the CFQUERYPARAM also ensures that you don't get '; BAD SQL INJECTION'
stuff getting through into your SET @p1 = '#myUserVar#' expression. Thank
Actually, I believe cfqueryparam uses bindings, which effectively passes
parameters to the SQL engine. In your example, you are still open to SQL
injection attacks.
However, if you need to use your value several times, you can use declare /
set to define a variable in SQL, rather than using multi
And for all those reading this and using MSSQL, an inline statement would look
like this:
declare @p1 nvarchar(50)
set @p1 = '#userSuppliedValue#'
select *
from tableName
where column = @p1
So, basically is creating the Declare and Set for you.
~~~
Update: You can actually enable multiple statements in a single query in
ColdFusion by appending allowMultiQueries=true to your datasource connection
string!
2009/4/16 David McGuigan
> Actually, it looks like as long as I managed my prepared statement naming
> manually across the entire MySQL
Actually, it looks like as long as I managed my prepared statement naming
manually across the entire MySQL server I'd be ok to just run multiple
queries in a row ( because CF8 with MySQL breaks for me if I try more than
one statement separated by semicolons ).
So it'd play out like this, for anyo
Am I correct in assuming ( after just having skimmed the topic with Google )
that to do that within CFML I'd have to drop into Java and use the MySQL
Java API to achieve that?
On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts wrote:
>
> > I've always been curious as to how cfqueryparam works. Does
Dave Watts wrote:
>> it somehow signals to the RDBMS that the
>> values are parameters to the query thereby treating an SQLI attack as an
>> escaped string or something?
>>
>
> It builds a prepared statement. It doesn't scan or filter anything.
>
Thus the database knows the data is data an
> I've always been curious as to how cfqueryparam works. Does anyone know if
> it just performs some scanning and filtering on the actual values of the
> parameters passed to it or whether it somehow signals to the RDBMS that the
> values are parameters to the query thereby treating an SQLI attack
I've always been curious as to how cfqueryparam works. Does anyone know if
it just performs some scanning and filtering on the actual values of the
parameters passed to it or whether it somehow signals to the RDBMS that the
values are parameters to the query thereby treating an SQLI attack as an
e
This is the link to the function code in case it was not apparent from my
post... I really need to slow down. ;o)
http://coz.pastebin.com/f588cde23
G!
--
Gerald Guido
http://www.myinternetisbroken.com
http://www.cfsimple.org/
"To invent, you need a good imagination and a pile of junk."
--
Bob
I have a function that sniffs out the CF data type based on the SQL
datatype. I am not sure if it is 100% as I have not tested it extensively.
But this should handle most of the MSSQL and MYSQL datatypes.
HTH
G!
http://coz.pastebin.com/f588cde23
On Thu, Apr 16, 2009 at 10:49 AM, BobSharp
> I have been searching for some explanation of
> the different Types used in CFQueryParam.
>
> understand that SCALE= is used to validate the position of decimal,
> but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4.
These correspond with specific database field types. You'd need to
k
Hi Bob,
Check here for a list of the available options and their corresponding data
types for some major SQL providers (for some reason MySQL isn't there):
http://www.cfquickdocs.com/cf8/#cfqueryparam.
As for your question, generally using cfqueryparam is recommended for any
value that could be p
I have been searching for some explanation of
the different Types used in CFQueryParam.
understand that SCALE= is used to validate the position of decimal,
but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4.
I am usingINSERT
do I need to use for all values ?
--
30 matches
Mail list logo
