Ghost Records, Card Recon and PCI Compliance


As part of a PCI Compliance audit, I recently ran a scan on a database using software call Card Recon.

A little odd thing occurred.

At a point in the past, a row of data in a column, which had been dropped from the database schema, contained a single test credit card number.

However, the Card Recon software showed that the data was still there in the database file (this was a SQL Database) in the form of a SQL Ghost Record. A Ghost Record can appear when running a delete or insert command and when running delete and insert in different queries but related by the same indexed data, you can read all about it over at Ghost "Rows" Buster in  SQL Server on Technet.

It's basically a record somewhere in the database file, but not directly in a database table and is living in a bit of spare fragmented space somewhere and this needs to be cleaned up.


Following this procedure managed to remove the Ghost Record:

  1. Convert the database to Simple (only so the transaction logs don't go mental)
  2. Back it up
  3. Shrink the database
  4. Rebuild all the indexes while in simple mode (a reorg helps sort out some fragmentation)
  5. ReOrg the indexes (not really required)
  6. exec DBCC CLEANTABLE
  7. DeFrag the Disk 
  8. DeFrag the database file with Contig (this will probably help a bit of performance if a heavily fragmented file)
  9. Execute DBCC FORCEGHOSTCLEANUP (this is undocumented so run it at your own risk)
  10. exec sp_clean_db_free_space for the database
  11. Update all the Statistics in the database
  12. Resize the database to its appropriate size (it was shrunk earlier)
  13. Convert the database back to Full 
  14. Do a full Backup

Side note 1; until I ran DBCC FORCEGHOSTCLEANUP; I executed the other steps several times and it didn't not clean up remove the ghost record, so clearly this is the key and most of the other steps could be run as a maintenance task.

Side note 2: most likely the Contig procedure should be run after resizing the database back to it's appropriate size as this would then keep the file in one piece!

Side note 3: If you want to see all the ghost records:

 SELECT DB_NAME(database_id), object_id, object_name(object_id) as Name, record_count, GHOST_RECORD_COUNT,Version_ghost_record_count,INDEX_TYPE_DESC, ALLOC_UNIT_TYPE_DESC, * 
        FROM sys.dm_db_index_physical_stats (null, NULL, NULL, NULL , 'DETAILED') where 
        index_level=0 and 
        GHOST_RECORD_COUNT = 1


Comments

  1. Anonymous28 December 2017 at 00:56

    Great blog... This blog clearly shows the importance of PCI compliance. Thanks for posting informative blog.

    ReplyDelete

Post a Comment

Popular posts from this blog

Global SQL Procedure, System Objects and sp_ms_marksystemobject

You may come across a need to have a database of utils full of generic procedures which work on indvidual databases (say client databases). You can't pass the database name into the procedure as a parameter and say "use @dbname" in the procedure and dynamic sql sucks. One workaround is to create the procedure in the master database and then mark it as a system object. eg use [master] create procedure sp_doThis // note the sp_ prefix is required begin // etc etc end go exec sp_ms_marksystemobject 'sp_doThis' // second note, this procedure is undocumented, so I wouldn't be relying on this for life or death. use [myotherdb] exec sp_doThis go All done!
Read more

cf_sql_timestamp vs cf_sql_date vs getdate()

If there's one thing I don't like it is people confusing a date with a timestamp, and how a lazy bit of development can ruin a load of data. Here is a simple query inserting data to a timestamp I found in a system where all the created dates were truncated because of the incorrect syntax. Bad one which was in use: insert into testdate values (<cfqueryparam cfsqltype="cf_sql_date" value="#createodbcdatetime(now())#">); Result :  2015-09-29 00:00:00.000 Best One (easiest to read) insert into testdate values(getdate() ); Result : 2015-09-29 10:10:09.880 Not great (no binding) insert into testdate values(#createodbcdatetime(now())#); Result :  2015-09-29 10:14:44.000 What the bad one should have been insert into testdate values(<cfqueryparam cfsqltype=" cf_sql_timestamp " value="#createodbcdatetime(now())#">)
Read more

Lucee 4.5.2 cfpdfparam difference with Adobe ColdFusion

Suppose the following on Lucee 4.5.2: <cfset x = 1> <cfdocument format="pdf" pagetype="A4" name="myVar#x#"> <cfdocumentsection> Hi there </cfdocumentsection> </cfdocument> <cfpdf action="merge" destination="RAM:///myPDF.pdf" overwrite="yes">     <cfpdfparam source="#evaluate("myVar#x#")#" /> </cfpdf> <cfcontent file="RAM:///myPDF.pdf" type="application/pdf"> then to check it also runs on Adobe Cf; you'd actually end up with: "ByteArray objects cannot be converted to strings."   The original code for Adobe CF ran with the paramater like so:  <cfpdfparam source="myVar#x#" /> Both sets of documentation list source defined as follows: "Source PDF file to merge. You can specify a PDF variable, a cfdocument variable, or the pathname to a file." This would indicate it is just...
Read more