[epidemik]

I am working on inserting the file name into the database, this is what I have so far, but I get a database excution error. Any suggestions on how i can get by this?

<cfset UploadFolder="#ExpandPath( '..\artist\pics' )#">

 <cfoutput>  
 <cfif DirectoryExists(UploadFolder)>  
  <cfif IsDefined("Form.UploadFile") AND Form.UploadFile NEQ "">  
      <cftry>  
             <cffile   
                 action="upload"   
                 filefield="UploadFile"   
                 destination="#UploadFolder#"   
                 nameconflict="overwrite"  
                 accept="image/*"  
                 >  
                 File uploaded successfully!  
                 <br />  
                 Uploaded file: #cffile.ClientFile#  

<cfquery datasource="#dsn#"> 
INSERT INTO Featured_artist (artist_picture) 
VALUES ('#cffile.ClientFile#') 
</cfquery>

<CFINSERT DATASOURCE="#dsn#" TABLENAME="Featured_artist" FORMFIELDS="artist_bio, artist_name, featured_posted, year_posted, month_posted">

<cflocation url="index.cfm?stat=featupdated"> 
             <cfcatch type="any">  
                 Error: #cfcatch.Message#  
             </cfcatch>  
      </cftry>  
     <cfelse>  
         Select a file first!          
     </cfif>  
 <cfelse>  
  Upload Directory not exists      
 </cfif>  
 </cfoutput>

[xheartonfire43x]

Well first off you are putting the wrong filename into the database. You should be putting cffile.serverfile which is a variable that holds the filename that was used on the server. Even though you are using nameconflict="overwrite" you should still use the server file variable.

Secondly your query should use the queryparam tag rather than just the variable. You are very open to hacking like that, even though it is a file field someone could name the file something malicious to kill your query.

Onto your actual problem at hand. I have a feeling that the error is a data truncation error. What I would suggest is, on a development box, remove the try/catch statement so that you get the full brunt of the error message or in your catch also give the cfcatch.detail to return the details of what the error is. If it does return a data truncation error, then your issue is that the field in the database only accepts shorter text that the filename being posted to it.

[epidemik]

I found my error, now its just working with the cfquery param you mentioned. Reading up on it to understand it more.

xheartonfire43x, on 20 February 2011 - 07:29 AM, said:

Well first off you are putting the wrong filename into the database. You should be putting cffile.serverfile which is a variable that holds the filename that was used on the server. Even though you are using nameconflict="overwrite" you should still use the server file variable.

Secondly your query should use the queryparam tag rather than just the variable. You are very open to hacking like that, even though it is a file field someone could name the file something malicious to kill your query.

Onto your actual problem at hand. I have a feeling that the error is a data truncation error. What I would suggest is, on a development box, remove the try/catch statement so that you get the full brunt of the error message or in your catch also give the cfcatch.detail to return the details of what the error is. If it does return a data truncation error, then your issue is that the field in the database only accepts shorter text that the filename being posted to it.

[xheartonfire43x]

Essentially what cfqueryparam does is escape all data passed into it. It helps to stop sql injection, one of the most popular and easiest forms of hacking.

An example of SQL injection is commonly used in URL variables. Say you have a query like this where the url is http://mywebsite.com/?page=3:

<cfquery name="getPage" datasource="#Application.dsn#">
     SELECT * FROM content WHERE pageid = '#url.page#'
</cfquery>

That is very easy to hack by changing the 3 to 3'; LIST TABLES; By doing that I will probably now get back a list of all tables in your database and if you have one called users then I can change my new url to 3'; SELECT * FROM users; which will give me all your users and probably passwords. I could steal all your users information and then drop your tables essentially screwing you over. You can see how dangerous it could get to not look into SQL injection and how to stop it. If your ever using dynamic variables in a query (something surrounded by # signs) you should be using cfqueryparam.

A good developer is one who knows how to hack.

This post has been edited by xheartonfire43x: 21 February 2011 - 12:50 PM

[epidemik]

This is an example that I have found of a cfqueryparam

<cfquery name = "getFirst" dataSource = "cfdocexamples">
    SELECT * 
    FROM courses
    WHERE Course_ID = <cfqueryPARAM value = "#Course_ID#"
    CFSQLType = 'CF_SQL_INTEGER'> 
</cfquery>

Just to make sure that I am understanding this clearly this is one of the codes that I am working on right now to change all the queries on my site.

<cfquery name="archive_list" datasource="#dsn#">
SELECT featured_posted, artist_name, year_posted, artist_id
FROM Featured_artist
WHERE year_posted = <cfqueryPARAM value = "#yr#" CFSQLType = 'CF_SQL_INTEGER'>
</cfquery>

I think I understand that part of it, do they have a list of different CFSQLTypes that can be used? what if its a VAR instead of an INT?

[xheartonfire43x]

There is a list in the livedocs - http://help.adobe.co...2c24-7f6f.html.

Also remember that the default cfsqltype is char, so you should just get into the habit of always setting it, because char is actually a rarer datatype to use in a db.

[epidemik]

xheartonfire43x, on 27 February 2011 - 09:46 PM, said:

There is a list in the livedocs - http://help.adobe.co...2c24-7f6f.html.

Also remember that the default cfsqltype is char, so you should just get into the habit of always setting it, because char is actually a rarer datatype to use in a db.

the link is broken

[xheartonfire43x]

Sorry. Not sure why it's broken, but anyways you can go to the Livedocs and look up cfqueryparam.