+1-888-365-2779
Try Now
More in this section

Forums / General Discussions / Where can I find the database schema and usage documents?

Where can I find the database schema and usage documents?

7 posts, 0 answered
  1. Ray
    Ray avatar
    10 posts
    Registered:
    06 Feb 2009
    23 Feb 2009
    Link to this post
    No, I'm not a developer. :-)

    We use Tripwire to monitor our web servers for unauthorized changes. It was rather easy in our pre-Sitefinity environment because there were actual files we could monitor. With Sitefinity it seems that the actual content displayed on the pages is created dynamically using elements from the SQL 2005 database.

    Nevertheless, we still need to monitor the web sites for unauthorized changes. Tripwire does allow us to query a SQL database and look for content changes. The queries can be a complicated as needed or something as simple as :

    SELECT * FROM DATA.DATA.ESYS_OPEN_LOANS

    I need something that can tell me which tables are used for what. We don't need to monitor anything that is not directly part of a web page being displayed to a visitor but we do need to monitor everything displayed to a visitor.

    With the huge number of SQL Injection hacks over the past few years, I'm sure you can understand why we need to protect our company's reputation.

    Thanks for any guidance you can lend,

    Ray
  2. Nikolai
    Nikolai avatar
    216 posts
    Registered:
    21 Nov 2016
    25 Feb 2009
    Link to this post
    Hello Ray,

    Since we use ORM for Sitefinity, there is no available documentation for the database. If you are more specific which action you want to monitor, we will provide you with more information about what is happening and how.

    Kind regards,
    Nikolai
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. Ray
    Ray avatar
    10 posts
    Registered:
    06 Feb 2009
    25 Feb 2009
    Link to this post

    Thanks, Nikolai. We need to know whenever content that is displayed to a web site visitor is modified.

    The sites are not interactive at all. We are monitoring the file server where Sitefinity is installed and we see a few changes there but not anywhere near what is actually being changed.

    In Apache we just monitored the /httpd folder and subfolders as needed. In IIS we just monitored the /inetpub/wwwoot folder and subfolders as needed. With other SQL databases we query the relevant tables looking for changes that occur without proper change control approval.

    Are there certain tables where the content that is grabbed by the web server is stored? We don't care about site revisions or anything else. We just need the content that is displayed to the visitor.

    Hope this helps,

    Ray

  4. Slavo
    Slavo avatar
    295 posts
    Registered:
    24 Sep 2012
    25 Feb 2009
    Link to this post
    Hello Ray,

    First, before I mention anything else, I want to assure you that SQL Injection attacks are not possible with Sitefinity. Every database query executed is constructed by our Object-Relational mapper and we don't insert user-inputted data into the queries.
    So if this is the reason you want to monitor database changes, I'm not sure if it's worth it.

    Then, if you still need to do this, I want to clarify that basically every Sitefinity module has its own table. Additionally, all modules based on Generic Content use the same table for storing content. It is hard to split what tables contain user-viewable content from the rest but I'll try to provide a non-exhaustive list here:

    sf_Polls_AnswerData
    sf_Polls_PollData
    sf_Polls_QuestionData
    sf_PageContentBase
    sf_PageBase
    sf_lst_NamesList
    sf_lst_ListItem
    sf_Libraries
    sf_GCMetaData
    sf_frm_Post
    sf_frm_Forum
    sf_frm_Category
    sf_Events
    sf_CmsTextContent
    sf_CmsTemplContent
    sf_CmsTag
    sf_CmsPageTemplate
    sf_CmsPageContent
    sf_CtrlLinks
    sf_ContentThumbnail
    sf_CmsContentBase
    sf_CmsComment
    sf_CmsCategory
    sf_CmsBinaryContent
    sf_Blogs

    Again, this list doesn't ensure that all public site information is contained in these tables. It also depends on what functionality of Sitefinity you are using and can be different with every site. Stil I hope it would help you in what you're trying to do.

    Best wishes,
    Slavo
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  5. Ray
    Ray avatar
    10 posts
    Registered:
    06 Feb 2009
    25 Feb 2009
    Link to this post
    Thanks. Tripwire uses this syntax:

    SELECT * FROM Database.Schema.Table

    Is the database and schema common for all of the tables you listed?

    And could you let me have them, please? :-)

    Ray
  6. Gabe Sumner
    Gabe Sumner avatar
    440 posts
    Registered:
    09 Sep 2007
    25 Feb 2009
    Link to this post
    Hey Ray,

    All the tables shown above are stored in a single database.  Once connected to the database, you can use:

    SELECT * FROM <table_name>

    I'm not sure this is going to give you the information you need though.  If I'm reading this correctly you want a single (or multiple) SQL queries that you can monitor.  These query results will be run by Tripwire and compared to previous query results.  Tripwire then uses these results to flag unauthorized changes.

    I don't know what those queries would be.  As Slavo said, I think it would depend on what features you are using.  The ORM that is part of Sitefinity generates these queries.

    I suppose you can simply monitor all the tables using the SELECT query shown above.  That will certainly give you a "heads up" about changes...but many of those changes might be very routine.

    Gabe Sumner
  7. Ray
    Ray avatar
    10 posts
    Registered:
    06 Feb 2009
    25 Feb 2009
    Link to this post

    Thanks, Gabe. Yep, you understand perfectly. Using Tripwire always involves seeing too many changes, figuring out which ones are normal and filtering those out. It can get tiresome. :-)

    I was hoping to get a list of tables used in the "live" site. That way I wouldn't have to worry about figuring out which ones were from test sites, old revisions, etc.

    Ray

Register for webinar
7 posts, 0 answered