Stored Procedures and SQL Injection

  • Sounds like an auditing firm making a determination not specified in the standard because they think it best. If it isn't in the actual standard how can the justify failing you if you aren't using stored procedures?

  • Remember that auditing firms make an attestation as to whether you're in compliance. That attestation is basically a statement of confidence. So, yes, it's entirely possible one firm would require something when another wouldn't if the requirement is not explicitly spelled out in the standard. For instance, way back in the day, this is how one firm we did business with got a SAS70 attestation even though they didn't patch their Windows servers. I wish I was kidding. Our auditors, on the other hand, required a detailed patch management plan along with verification that the controls were being met before they signed off each time for our SAS70 attestation. Why the difference? SAS70 was very vague and didn't have anything specific with regards to those sorts of controls.

    K. Brian Kelley

  • Dave Vroman (2/19/2013)

    It totally depends on the level of PCI compliance. I don't remember where it was and I am no longer at that company. It was not specifically spelled out in the compliance papers, but it was required by the company that was testing for compliance.

    Sounds like the auditor was just making stuff up.

    Not that I think using stored procedures is a bad thing, but this sort of thing is why I have very little respect for firms that do PCI, SAS70, SOX, etc. audits.

    My experience is that they zoom in on petty items while they ignore or don't even understand serious problems.

  • Have a good read of

  • The first rule of application security is to never trust user input. The second rule is always test for sql injection. The third rule is to run the tests again in the stored procedure. The third rule is to never allow any query other then through a stored procedure. The fourth rule is prohibit the use of Entity Framework to directly access the database without using a stored procedure.

    As much as I hate the first word that comes out of a DBA's mouth, "NO!!!", security of the data in a database is the single most important value an application developer can hold is security. The second most important is maintainability.

    When I have been engaged as an architect on a project, I have always considered security to be the most important consideration and have angered many developers by refusing to move off the position that stored procedures are the only way to access the database.

  • I agree with this article - security tops the list when building and maintaining data for our users and ourselves.

    From a database developer viewpoint, I prefer to require all table (and even view) access be done through stored procedures. Reason being, in time, things change. An excellent design of a table and it's appropriate indexes (or indices???) along with partitioning, and a myriad of other properties of the table doesn't mean it will always be a good design. People add functionality to a computerized system and jury-rig stuff into a table, the amount of data in a table may grow too large for the original design, etc. Bottom line... something changes in the database which makes the original table (and view) design inadequate. With Stored Procedures, you can add columns, delete or rename columns, or use a different table object, or even create a view that is used by the stored procedure. All you have to do is change the Stored Procedure and the other layer in your project (business layer or front-end) don't need changed. Nothings perfect, but we can strive for perfection.

    It's been around 20 years since my initial training on database theory and terminology, but I believe this helps support the ACID acronym.

  • In regards to PCI Compliance, the auditors have the required standards to test and then anything as they deem. This was because if some new vulnerability comes out they dont have to wait to wait for a new rule by committee. The bad is they can make it up as they go along. I had a site get flagged because of the specific order that the response headers were transmitted.

    I recently found that one of my sites actually sql-injected itself; putting data (hardcoded) in was perfectly fine with its single-quote because the scripting used parameters. A senior-to-me developer added in some new scripting to access this, but used string.format instead of parameterizing it. idiot

    Director of Transmogrification Services
  • Loved the article but it does not address an injection string of the type Usename:= "(Select * from Internet.Students)" where the configured user has access to execute against the internet.Students table.

  • Loved the article but it does not address an injection string of the type Usename:= "(Select * from Internet.Students)" where the configured user has access to execute against stored proc against the internet.Students table.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply