Serious Problem I NEED HELP 4

[brantGuy]

Someone is deleting my mssql database tables...

how do I stop them and haw can I find out who...

Someone will be in for one seriouse buttkicking if I can ever get my hands on them...

 

[imstillatwork]

hosted or your own server?



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

access or sql /mysql

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[brantGuy]

hosted

http://www.hostingleadet.net

and its ms sql

 

[imstillatwork]

can you change your password on the DB? do that NOW if you can, then updated the password for the DSN

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

Sorry, you should have contacted your service provider BY PHONE first. did you?

you SURE you don't have code running somewhere that is doing this? for %100?

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[brantGuy]

Called them but no one there...Will call in the morning...

100% sure there is no code on my site to drop a table.

I had a similar thing happen a few years ago to the same site...got so bad that I closed down that partof my site, unfortunatly, that section draws over 1,000,000 hits a year so I wanted it back...Its only been relaunched 3 weeks and was in beta testing...and I was hit again...Im bettin it was the same people

 

[Sarky78]

Are you using cfqueryparam on your database queries?

Sounds to me like people are doing SQL Injection into the database using a url param, and droping the table. Something that is easily stopped by using cfqueryparam, like this:

<cfquery datasource=""....>
  select a, b, c
  from tbl1
  where e = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.Value#">
</cfquery>

the cfsqltype can be one of a lot of things and will depend upon your requirement. have a look at the cfdocs for information on what to put into here.

what will happen is the param will throw an error if the passed value can't be converted to the specified type, so wrap the query in a cftry/cfcatch, and display a nice error to the user. This will also have the added bonus of being able to gather information about the culprit, such as the ip address. You could do something like:

<cftry>
  <cfquery datasource=""....>
    select a, b, c
    from tbl1
    where e = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.Value#">
   </cfquery>
   <cfcatch type="Any">
     <!--- error trapping --->
     <!--- could do a cfinclude here for an error file --->
     <cfmail to="you" from="website" subject="error">
       <cfoutput>#cgi.remote_addr#</cfoutput>
     </cfmail>
   </cfcatch>
</cftry>

you could also take a look at this article by Ben Forta for more information on how it is being done, and alternate ways to stop it:

http://www.forta.com/blog/index.cfm...-Easy-To-Prevent-But-Apparently-Still-Ignored

Hope this helps!

Tony

 

[ecobb]

I think Sarky78's right. This is almost certainly SQL Injections, or someone else has access to your database.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[brantGuy]

Ok im gonna post my sql here and hopefully we can fine where the hole is

REGISTER

<cfquery datasource="#DSN#" name="qLogin" dbtype="oledb">
select username, password, status, userLevel
from users
where userName = <cfqueryparam value="#TRIM(form.userName)#" cfsqltype="cf_sql_char"> and password = <cfqueryparam value="#TRIM(form.password)#" cfsqltype="cf_sql_char"> and status = 'Approved'
  </cfquery>

NEW THREAD

<cfquery datasource="#DSN#" name="qNewMessage" dbtype="oledb">
INSERT into threads
(username, title, message, ipAddress, msgSent, updated) 
VALUES 
('#TRIM(cookie.ODRMember)#', '#TRIM(Form.title)#', '#TRIM(Form.message)#', '#CGI.remote_addr#', #now()#, #now()#)
  </cfquery>

RESPOND THREAD

<cfquery datasource="#DSN#" name="qNewResponse" dbtype="oledb">
INSERT into Response
(threadId, username, message, ipAddress, msgSent) 
VALUES 
('#TRIM(Form.threadId)#', '#TRIM(cookie.ODRMember)#', '#TRIM(Form.message)#', '#CGI.remote_addr#', #now()#)
  </cfquery> 
 <cfquery datasource="#DSN#" name="qUpdateThread" dbtype="oledb"> 
 UPDATE Threads
 set updater = '#TRIM(cookie.ODRMember)#', updated = #now()#
 where threadId = <cfqueryparam value="#TRIM(form.threadId)#" cfsqltype="cf_sql_integer">
 </cfquery>

Please, a couple of quers dont have the cfqueryparam but I was not sure how to place them there...

and even theones that are there, im not sure are correct...

 

[imstillatwork]

<cfquery datasource="#DSN#" name="qNewMessage" dbtype="oledb">
INSERT into threads
(username, title, message, ipAddress, msgSent, updated)
VALUES
('#TRIM(cookie.ODRMember)#', '#TRIM(Form.title)#', '#TRIM(Form.message)#', '#CGI.remote_addr#', #now()#, #now()#)
  </cfquery>

cfqueryparam is used exactly like you used it, were you actually used it...all you need to do it is choose the correct cfsqltype that matches your field, and the value attribute

!!!!!!!!!!!!!

FIX THAT NOW! You are allowing FORM data to DIRECTLY EFFECT the query if someone so wanted to, ESPECIALY in MSSQL, which allows multiple queries per request separated by the ';' (MySQL allows this, but NOT by default.)


CFQUERYPARAM is a MUST for any query that takes an outside variabe (one whos value does not originate from your own script... url,form,cookie, any variable that's value originates from one of those, etc...)

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[brantGuy]

Im not sure what to include on it with the insert command

 

[imstillatwork]

like I said, you use it just like you used it before.

insert into mytable(field1,field2)values(
<cfqueryparam cfsqltype="chooseone" value="#yourvalue1#">
,<cfqueryparam cfsqltype="chooseone" value="#yourvalue2#">
)

use the cfqueryparam tag where a coldfusion variable would have been

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[brantGuy]

is this right....

and is the cfsqltype right as well

<cfquery datasource="#DSN#" name="qAddGuestBook" dbtype="oledb">
INSERT into guestbook
(name, hometown, email, message, ipAddress, dateposted, display) VALUES (<cfqueryparam cfsqltype="cf_sql_char" value="#form.name#">, <cfqueryparam cfsqltype="cf_sql_char" value="#Form.hometown#">, <cfqueryparam cfsqltype="cf_sql_char" value="#Form.email#">, <cfqueryparam cfsqltype="cf_sql_char" value="#Form.message#">, <cfqueryparam cfsqltype="cf_sql_char" value="#CGI.remote_addr#">, <cfqueryparam cfsqltype="cf_sql_date"  value=#now()#>, <cfif #TRIM(form.name)# eq #TRIM(form.homeTown)#>'No'<cfelse>'Yes'</cfif>)
  </cfquery>

 

[brantGuy]

when I use cf_sql_date and apply it to #now()# it eliminates the time...

is there something else I can use here or do I even need to include something with a date/time cause its format is already very limited

 

[ecobb]

...is the cfsqltype right as well

We don't know, you're the only person who can answer that. The cfsqltype has to match whatever datatype that particular field is in the database. If it's a Char, then the cfsqltype has to be Char, if it's a Bit, then the cfsqltype has to be Bit, etc...

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[brantGuy]

Thanks Kevin and everyone

I added the code for the cfqueryparam around everything except #NOW()#

I created an empty Table called "DropMe"

my site is

http://www.offdarock.com

forms available are
in the guestbook
ghoststories and the virtual kitchen party

would you (if you have the time) try to drop the table called "DropMe"

Thanks

 

[ecobb]

Use timestamp instead of date for your cfsqltype and it will put the date and time in there for you.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[brantGuy]

that helps alot, thanks ECAR

 

[Sarky78]

tried to test your guestbook earlier, but got a cf error on submitting to the db. have you put the suggestion from ecar in place yet? If so are the fields that you are sending a datetime to that field type in the database?

Error:
[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting datetime from character string.

Tony