Well, at least it pays....



  •  So I'm working on a small-ish company's web application that I was told they needed ported to .Net from VB6 + Classic ASP.  The code is a mixture of stuff that was done by in-house developers and off shore developers.  Some of the gems I'm finding:

     

    Sometimes they use source control, sometimes they don't.  I guess it depends on what mood they're in that day.  It makes it fun to figure out when a stored procedure was modified in-place and never exported and checked into source control.  Has the change only been done in the test environment?  Was it done in production and propagated to test?  Was it tested and promoted to production?  Who knows...

    Someone actually wrapped ADO and used it heavily in some areas of the application.  It wraps everything in a single function call that has the parameters giving the name of the stored procedure and a single dimension variant array that you're supposed to use to pass in the literal arguments to the Parameter.add method (like 4 arguments).  It, of course, returns a variant that has the results. 

    The database connection parameters are categorized into several categories, each category represents some part of the application.  These are each implemented as a registry key.  In each category, there is a spot to store a connection string, the name of an ODBC data source, and the individual components of the connection string.  Some parts of the app use ODBC, some use direction connections and either build their own connection string or use the pre-defined connection string.  There are 2 different objects created that access these items from the registry.  One runs local, and one is remoted.  Some apps get their database settings locally, some of them get them from the remoted component.  Generally, all the settings are the same across all machines.

    They failed to grasp the purpose of remoting altogether.  In some cases, the database is accessed through a remoted DAL component.  In some cases, through a local component..  In other cases, someone just said "screw this" and talks to the database via ADO directly from the application.

    They have a common connection configuration category also.  It only defines the connection timeout value.  Nothing else.  All other categories connect to the same database since there is not case where the hundreds of tables in the database aren't dependent on some other table so much that there is no way to parition responsibility at all.  

    Some dude commented out the part where the database user's name is obtained from the connection registry and put his own name in there as a string literal.  He then made it so it used the connection timeout registry value as the password.  His account is also required to be part of the sysadmin role due to some third party tool that apparently can't figure out how to query a database without using administrative facilities.  He doesn't even work there anymore; instead of fixing it, they've just been adding his name/password to the sysadmin role of each DB instance.  This has been going on for more than a year.

    There is no referential integrity between any tables.  It is all enforced on the application layer.  You're just not allowed to delete anything.

    I'm sure there is a lot more yet to find....



  • @ooblek said:

    I'm sure there is a lot more yet to find....
    The door?



  •  For a moment I thought you said you were porting it FROM .net TO vb6...



  • @ekolis said:

     For a moment I thought you said you were porting it FROM .net TO vb6...

    That would be awesome*

     

     

     

     

     

     

     

     

     

    *for certain values of awesome

     



  • Found several places throughout the legacy app we're rebuilding:

    Me.SqlConnection1 = New SqlConnection("initial catalog=[some database], user id=[user id], password=[password], workstation id=SENTIL-GANAPESHAN")
    

    Yes, hard-coded in the domain objects. Well, some domain objects--we use a homegrown ORM (I use that in the vaguest sense of the word), which has gone through several versions, each with a very different way of interacting with the database. All of them require you to specify CRUD sproc names in the domain object, but one version makes you add all the columns/properties in the object in code, while another version uses a SQL Server feature that I previously didn't know existed, where it queries the database for what parameters go into each sproc. Oh, and the custom ORM's object collection doesn't implement ICollection (or an indexer).



  • OK. Not impossible to do, but a nice challenge.

    I'd like:
    - one good analyst
    - one architect
    - one DBA
    - a couple experienced .Net developers (or one really good ones and a couple smart juniors)
    - a project manager

    Given the amount of detail you've given, I'd estimate it should take a team of 6-8 people somewhere between 6 months and a year; if this is a tough poplitical environment where you have lots of discussions about useless functionality still "HAS TO STAY IN", 2-3 years.



  •  Well, your first mistake is that you're estimating the amount of time "it" will take given those resources.  If you're not the architect or one of the senior programmers, you shouldn't be making any estimates.

     

     Sad, but true.



  • Actually, I sort of expected you to give a bit more info about the size of the team. In fact, I more or less thought you'd go "I'm supposed to do it all alone in 3 months", and then I could commiserate with your predicament.



  •  Just found that there are DB connection strings stored in the DB.  Nice plain-text password stored there for whoever wanted to discover it.

     

    So the process goes:

     

    1. Log into application (this happens to be a .Net app).  This app uses 2 connection strings in the web.config: one is for reporting, the other is for the application data (logins, etc).

    2. Run a query on the reporting database using the reporting connection string in web.config to get the connection string used to connect to the database for the report.  (Strangely enough, the connection string to the reporting database is also in the database.  I guess it was just in case they got lost....)

    3. The return value is the connection string for the application database...the one they already have in web.config.  So the reports aren't actually in the reporting database, they are in the application database.  You need the reporting database to figure that out.

    I also noticed that the reporting database has a stored procedure in it that does a join with an application database table.  It is references the application db by name, so if you name your application database something other than expected, things break.

     

     



  • @ooblek said:

    It is references the application db by name, so if you name your application database something other than expected, things break.

     

     


    That is actually expected. Or did you want to make 'things' search the master table, looking for a database with an appropriate schema? ^^



  • @Shortjob said:

    @ooblek said:

    It is references the application db by name, so if you name your application database something other than expected, things break.

     

     


    That is actually expected. Or did you want to make 'things' search the master table, looking for a database with an appropriate schema? ^^
    This makes building non-production instances of the application an "eventful" process.  I've spent years of my life un-spaghetti-ing databases built like this.


  • @ooblek said:

     Just found that there are DB connection strings stored in the DB.  Nice plain-text password stored there for whoever wanted to discover it.

    Isn't the same problem present in config-file-based connection strings? Unless you're encrypting your connection strings (which, of course, you could do if you have database-stored connection strings as well), or are using integrated security, you're going to have passwords in plaintext somewhere.



  • Storing db connection strings in a db is like locking your key in a safe.



  • This isn't neccessarily always true.  As you pointed out, integrated security is one way to do it.  It has its own problems, but that is one method.

    According to some PCI auditors, it is acceptable to embed a static symmetric encryption key into an application for encrypting things like connection strings.  That is sort of a WTF in and of itself, but it works.

    You also have ACLs that can control who can view the connection information.  As one poster pointed out, putting connection strings in your DB is like locking your key in the safe.   My point was actually that it was looking up the connection strings from the app that has to already have the connection strings to get there in the first place.

     



  • @b-redeker said:

    Storing db connection strings in a db is like locking your key in a safe.

    We used to store DB connection strings in our DB... each project got its own database to track into, then we had a master "admin" database that could look up a product by name and return the connection string to connect to its database. I'd consider that a perfectly valid use-case.

    Of course, this was all using Active Directory authentication, so there weren't any passwords stored anywhere.


Log in to reply