locked
A third party software is sending nasty SQL our server RRS feed

  • Question

  • A third party software is sending nasty SQL our server....

    for example Select * from Table1.

    Is there a way in which I can use PLAN GUIDES to restrict which columns the select statement uses.

     

    There is an error showing up on this page when I try and reply to an answer.


    Mr Shaw

     

     

    • Edited by Mr Shaw Wednesday, January 26, 2011 11:51 AM
    Wednesday, January 26, 2011 11:42 AM

Answers

  • The 3rd party application is nhibernate which the C# Development team use when writing there web pages.

    OK. That is not really what I think of when I hear "3rd party app."

    the current nhibernate setup does not let you modify the SQL and it produces SELECT * Statements.

    I have never worked with nHibernate, but I've heard a lot of bad things about it.

    The idea of using views sounds good as I will be able to control what is in the SELECT * statements.


    You will need cooperation from your developers. That is, if they need to access columns a, b, and c from tblA in place and in another place they need to access a, d, and g, you will need different views. And the programmers need to know that they should use different views.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Kalman Toth Wednesday, February 2, 2011 11:25 AM
    Wednesday, January 26, 2011 3:39 PM

All replies

  • Do you want to restrict the columns that the software is requesting in the query?  so it only requests

    select userid, username from Table1

     

    (for example)

    Wednesday, January 26, 2011 11:48 AM
  • Do you want to restrict the columns that the software is requesting in the query?  so it only requests

    select userid, username from Table1

     

    (for example)

    Yes
    Mr Shaw
    Wednesday, January 26, 2011 11:52 AM
  • I used quote to reply to your answer
    Mr Shaw
    Wednesday, January 26, 2011 12:04 PM
  • one way is u can create an view with some name and in that view you can select only specific columns. and you can expose that viewname to your clients or those people........
    Wednesday, January 26, 2011 12:05 PM
  • Is this beyond the scope of PLAN GUIDES
    Mr Shaw
    Wednesday, January 26, 2011 12:11 PM
  • its not involved with plan guides... i'm not sure how to do this with help of plan guides....its about creating a layer on top of table and exposing that.....
    Wednesday, January 26, 2011 12:23 PM
  • A third party software is sending nasty SQL our server....

    for example Select * from Table1.

    Is there a way in which I can use PLAN GUIDES to restrict which columns the select statement uses.


    No, you cannot use a plan guide to change the result of a query.

    As Rami Reddy says, you could rename the table and replace it with a view. Hoever, I would strongly recommend against that. The risk is that you will broke the application is considerable. You may not know which columns it uses. Furthermore, the application by reference columns by numeric index (very, very bad practice). And if course, any support obligation you have with the vendor is likely to be void if you meddle in that way.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, January 26, 2011 12:27 PM
  • The 3rd party application is nhibernate which the C# Development team use when writing there web pages.

    the current nhibernate setup does not let you modify the SQL and it produces SELECT * Statements.

    The idea of using views sounds good as I will be able to control what is in the SELECT * statements.


    Mr Shaw
    Wednesday, January 26, 2011 12:43 PM
  • The only restriction is that I can not pass parameters to a view.


    Mr Shaw
    Wednesday, January 26, 2011 12:44 PM
  • The only restriction is that I can not pass parameters to a view.


    Mr Shaw

    Use a stored procedure.
    Ctrl+Z
    Wednesday, January 26, 2011 1:13 PM
  • You could use a view, and a stored proc with parms to pull data from that view.
    Wednesday, January 26, 2011 2:13 PM
  • A bit unrelated, but this is a good blog about configuring nHibernate

    nHibernate making updates and inserts much faster

    and you can also ask your question.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 26, 2011 2:48 PM
  • The 3rd party application is nhibernate which the C# Development team use when writing there web pages.

    OK. That is not really what I think of when I hear "3rd party app."

    the current nhibernate setup does not let you modify the SQL and it produces SELECT * Statements.

    I have never worked with nHibernate, but I've heard a lot of bad things about it.

    The idea of using views sounds good as I will be able to control what is in the SELECT * statements.


    You will need cooperation from your developers. That is, if they need to access columns a, b, and c from tblA in place and in another place they need to access a, d, and g, you will need different views. And the programmers need to know that they should use different views.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Kalman Toth Wednesday, February 2, 2011 11:25 AM
    Wednesday, January 26, 2011 3:39 PM
  • The architect has to agree to using views instead of access tables directly.

     


    Mr Shaw
    Wednesday, February 2, 2011 10:52 AM