Coffeehouse Thread

30 posts

Eliminate SQL Injections for good...

Back to Forum: Coffeehouse
  • User profile image
    Manip

    I think SQL Injections can and should be eliminated and I think I have a practical, safe, and cheap way to do it. I am directing this at no language or framework in particular, for all I know it might have already been implemented.

    SQL Injection happens when someone inputs a specifically formatted string into a method which in turn doesn't reject or modify the string enough and then passes it on to the SQL server. The SQL server, receiving a correctly formatted query string will execute its instructions.

    History has shown that people are idiots and have no idea how to write an string parser; thus writing insecure web and desktop applications.

    My suggestion is simple: Add a new 'type' call it whatever you want, but I like 'SQLQuery.' The real trick is not with the new type, it is with the SQL server; the server *MUST ONLY* support this type for queries. No standard strings, no chars, no long strings, nothing...

    The SQLQuery type has two methods of interest:

    *.addQueryString(String query)
    *.addText(String text)

    The first one takes raw SQL queries just like you can execute today. The second appends text onto the query string but with all the required formatting / scanning applied to it.

    Now of course this is only a vague and simplistic suggestion, and of course the programmer could add strings using *.addQueryString() and some would. But at least with the above they have no excuse for doing so.

  • User profile image
    blowdart

    Eliminate SQL Injections for good...
    (use dbase II)

  • User profile image
    Tom Servo

    What's wrong with this:

    SqlCommand cmd = new SqlCommand("select * from blah where param like @param");
    cmd.Parameters.Add("@param", SqlDbType.NVarChar).Value = "%lols%";

  • User profile image
    MisterDonut

    Stored Procedures go a long way, too. Added to their compilation, they're great for data access.

  • User profile image
    blowdart

    MisterDonut wrote:
    Stored Procedures go a long way, too. Added to their compilation, they're great for data access.


    But ...

    how are you going to page data when you cannot do

    select top @parameterName ids from table

  • User profile image
    littleguru

    Well - Sql injection is eliminated. Simply use parameters as suggested here. If you need the top 100 or something, just use a DataReader and get the first x items and close the reader afterwards.

    The problem with sql injection was that people simply did not know how to use parameter and did simple string concat. String concat is also vers slow, btw.

    Just reflect a little bit before writing your query and use parameters. That's it. It's bad practise to allow the user to set parts of the query as parameter of the page url...

    Foo.aspx?Table=FOO&Column=BAR

    Something like this is never required. There is always a way to get around this problem. It's a little bit more work, but there is always another way.

  • User profile image
    Tom Servo

    blowdart wrote:
    select top @parameterName ids from table


    select top (@parameterName) ids from table

    ...well, at least in SQL Server 2005.

  • User profile image
    blowdart

    Tom Servo wrote:
    blowdart wrote:select top @parameterName ids from table


    select top (@parameterName) ids from table

    ...well, at least in SQL Server 2005.


    But not before 2005, if you want to parameterise top it's dynamic SQL for you.

    Oh, and in 2005, use row_number() for paging, not two nested tops Smiley

  • User profile image
    W3bbo

    blowdart wrote:
    Oh, and in 2005, use row_number() for paging, not two nested tops


    ...ROWCOUNT does the job in SQL 7 and 2000

  • User profile image
    blowdart

    W3bbo wrote:
    blowdart wrote:Oh, and in 2005, use row_number() for paging, not two nested tops


    ...ROWCOUNT does the job in SQL 7 and 2000


    Kind of, assuming you have an identity field you can filter on as well, in which case you may as well use BETWEEN.

  • User profile image
    Manip

    I very clearly stated I was not directing this at one framework or language. Seems everyone has read it as -- "In the .Net framework..."

    littleguru wrote:
    String concat is also vers slow, btw.


    And adding a parameter is faster? ... Right....

  • User profile image
    littleguru

    Manip wrote:
    I very clearly stated I was not directing this at one framework or language. Seems everyone has read it as -- "In the .Net framework..."

    littleguru wrote: String concat is also vers slow, btw.


    And adding a parameter is faster? ... Right....


    Well. String.Concat is a quick and dirty thing. You should not do it with SQL. Avoids a lot problems. To avoid sql injection just escape all single and double quotes. That's everything you need to do.

  • User profile image
    littleguru

    Manip wrote:

    And adding a parameter is faster? ... Right....


    Yes! Database systems have caching functionalities. Once you have a query with parameters that query is cached (the so called query plan is create and cached). Afterwards the same query with other parameters is a lot faster!

    Look at the MS SQL Server, Oracle, ... documentation. Or simply read a book about database design and query parsing and analyzing in a database system.

  • User profile image
    W3bbo

    littleguru wrote:
    To avoid sql injection just escape all single and double quotes. That's everything you need to do.


    Wrong.

    What if the parameter is a non-delimiteted number?

    What if someone injects a SQL Comment "--" into the parameter?

    And many other possibilities Smiley

  • User profile image
    blowdart

    littleguru wrote:
    To avoid sql injection just escape all single and double quotes. That's everything you need to do.


    Really, it's not.

    For example exec will also take hex encoded commands, so if you were silly enough to exec completly raw SQL, then I could do something like
    select master.dbo.fn_varbintohexstr(CAST('drop table syslogins' as
    varbinary))

    This returns


    0x64726f70207461626c65207379736c6f67696e73

    which you can actually exec Smiley Then of course there's escaping the escaping, especially when truncation comes into effect; for example user''''''''''' Now, you escape that and you get user''''''''''''''''''''', but if it's truncated to 25 characters because that's the limit in your database, lo, you've got an unescaped '. BANG!

  • User profile image
    littleguru

    W3bbo wrote:
    littleguru wrote: To avoid sql injection just escape all single and double quotes. That's everything you need to do.


    Wrong.

    What if the parameter is a non-delimiteted number?

    What if someone injects a SQL Comment "--" into the parameter?

    And many other possibilities


    Well for if you escape the double or single quotes a -- is not working. '--' is not parsed as comment start. Only inserting -- does not work. And with escpaing the quotes it does not work.

  • User profile image
    littleguru

    Cool. I never did hex encoding of commands. Does this really work? Is this parsed by the database system? Which database system parses hex commands? How is this done? Only for curiosity...

  • User profile image
    blowdart

    littleguru wrote:

    Cool. I never did hex encoding of commands. Does this really work? Is this parsed by the database system? Which database system parses hex commands? How is this done? Only for curiosity...



    SQL 2k does it, not sure about older versions.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.