Must be related to Bobby





  •  Remind me again, what's the point of ColdFusion? I mean there are much better languages out there and if we want something to laugh at we can always look at PHP.



  • @Renan said:

    I wonder if his parents knew what they were doing.

    Given that it's his last name, it's not really their fault.



  •  Yeah, but they named him PointerException



  •  The problem with "always use parameters, never try to escape" advice on that Bobby Tables site is that it only works well for immediate commands, not when you have to generate a script.



  • What, you can't auto-generate scripts to use parameterised queries now?



  • Wait, do you mean files that the database will essentially just eval? If so, then why would you be creating them with data from an untrusted source?
    Sounds like a bad idea just waiting to happen.



  • @Salamander said:

    If so, then why would you be creating them with data from an untrusted source?

    Sounds like a bad idea just waiting to happen.

    Maybe there's some reason you have to. Or maybe it's not untrusted data; you should still be escaping trusted data, you know. I don't think using escaping is bad if you are using a routine provided by your database library, but you should prefer prepared statements wherever possible. That site sucks because: 1) they're "teaching" by diktat and rote memorization, rather than explaining the advantages and disadvantages; and 2) it's based on a retarded comic.



  • We recently had to parameterize all the queries in our current project, even though all the data comes from "trusted" sources. The reason? Some of the data we encountered has legitimate single-quote characters in it, and building SQL statements using naive string concatenation would fail on those. Using parameterized queries is almost* always the right thing to do.

    * "Almost" included for the pedantic dickweeds, I'm sure there is one case somewhere where parameterized queries are a bad idea.



  • @mott555 said:

    The reason? Some of the data we encountered has legitimate single-quote characters in it, and building SQL statements using naive string concatenation would fail on those.

    WHO COULD HAVE SEEN THAT COMING?

    And that's why you always leave a note parameterize/escape data, even if it's "trusted".

    @mott555 said:

    Using parameterized queries is almost* always the right thing to do.

    It's definitely preferable (assuming it's available from your client library) which is what I said, but that doesn't make escaping (using the client library) incorrect, which is what the site tries to imply. Really, their half-assed answer is just spreading nonsense which is probably just going to create more problems down the road.



  • @Scarlet Manuka said:

    @Renan said:
    I wonder if his parents knew what they were doing.
    Given that it's his last name, it's not really their fault.
    His first name is Dev.  Every time I send him an email, it disappears.



  •  Another reason to parameterise your queries is because certain database drivers (such as Oracle's 'thin' JDBC driver) cache statements, and it's a lot more efficient to cache a single prepared statement than a million statements that only differ by their parameters.



  •  @Salamander said:

    What, you can't auto-generate scripts to use parameterised queries now?

    And then you have to escape the set @parameter= (or however it's done) statements.

    By the way, it is indeed trusted data that can contain single quotes. Can contain anything, actually. And yet DB providers like Microsoft not only don't provide escaping functions, but don't even document whether doubling single quotes is all you need to do!

     



  • @Medinoc said:

    By the way, it is indeed trusted data that can contain single quotes. Can contain anything, actually. And yet DB providers like Microsoft not only don't provide escaping functions, but don't even document whether doubling single quotes is all you need to do!
    As pointed out before, escaping is, well, daft.

     



  • @Medinoc said:

     @Salamander said:

    What, you can't auto-generate scripts to use parameterised queries now?

    And then you have to escape the set @parameter= (or however it's done) statements.

    By the way, it is indeed trusted data that can contain single quotes. Can contain anything, actually. And yet DB providers like Microsoft not only don't provide escaping functions, but don't even document whether doubling single quotes is all you need to do!

     

    You've never used ADO.NET, have you? Because it takes care of all of these issues without the programmer having to do anything. Which is correct, since the DB itself does not, and should not care about escaping input; it's the job of the provider of that input to sanitise before passing it through.



  • @The_Assimilator said:

    You've never used ADO.NET, have you? Because it takes care of all of these issues without the programmer having to do anything. Which is correct, since the DB itself does not, and should not care about escaping input; it's the job of the provider of that input to sanitise before passing it through.

    I have no problem when it comes to doing queries/modifications in an application (I have the reflex to use parameters) but I didn't know ADO.Net could generate a plaintext SQL script for you (sorry, when your boss wants one, a C# application just isn't the answer).



  • @Medinoc said:

    I have no problem when it comes to doing queries/modifications in an application (I have the reflex to use parameters) but I didn't know ADO.Net could generate a plaintext SQL script for you (sorry, when your boss wants one, a C# application just isn't the answer).

    Apparently it can...

    SqlCommand.CommandText property



  • @ekolis said:

    @Medinoc said:
    I have no problem when it comes to doing queries/modifications in an application (I have the reflex to use parameters) but I didn't know ADO.Net could generate a plaintext SQL script for you (sorry, when your boss wants one, a C# application just isn't the answer).

    Apparently it can...

    SqlCommand.CommandText property

     Parameterized in, parameterized out. And it won't give you a script that set the parameters.



  • @Medinoc said:

    And yet DB providers like Microsoft not only don't provide escaping functions, but don't even document whether doubling single quotes is all you need to do!

    They don't provide it because you're not supposed to be doing it.

    They also don't provide software for planning a genocide for much the same reason.



  • @Medinoc said:

    but I didn't know ADO.Net could generate a plaintext SQL script for you (sorry, when your boss wants one, a C# application just isn't the answer).

    What kind of plain text SQL script could your boss possibly want that may or may not contain unescaped data? What strange fantasy scenario are we describing here? Do you work in the Phantom Zone?

    Your gripe is true, but an understanding of what possible situation could call for that ball of WTFery would be welcome. Otherwise, I'm just going to assume you're making up crazy shit just to make your point.

    To clarify:
    1) If you're trying to share a schema, there's no data involved so its a non-issue
    2) If you're trying to export a table into a .sql file, SQL Server Management Studio does that easily
    3) If you're trying to import data, use SQL Bulk Insert, Import/Export Wizard, or write a 10-line C# program, all handle escaping correctly

    The only WTF-y example I can think of is your "boss" has a spreadsheet full of data he wants to insert, but doesn't know about Import/Export Wizard, and therefore wants to compose a SQL query by concatenating text in an Excel column, then copy/pasting into SSMS to run. That's... that's the only scenario I can think of. And the solution isn't, "yessir lemme write that script for you sir need your car waxed sir", it's "hey, lemme show you this neat tool built specifically to do exactly what you want..."



  • At some point we needed a script with all the contents of several tables in the form of INSERT statements, usable in several different database engines. Looking back at it we could probably have exported it all as CSV and imported it all in the other database, but at the time we had a problem with the Oracle tools or lack thereof (we are more used to SQL Server).

     So I was tasked with generating the script with some C# code, and while looking for a clean, "official" way to escape the strings (because of course some contained apostrophes using the single-quote character), I found none. When I asked for it on MSDN, I was told "there is none, use parameters". In the end I just escaped the single quotes and prayed.

     @blakeyrat said:

    2) If you're trying to export a table into a .sql file, SQL Server Management Studio does that easily

     It does? The most intuitive way to do so "Right-click on the table -> Script table as -> INSERT" doesn't and while I'm checking the import/export wizard, I don't see suitable destination type (the closest is "flat file").



  •  In fact, if anyone tells me where the means to export as .sql file is in SQL Server Management Studio, I'll apologise for wasting your time, take back anything I said about Microsoft and escaping, and never use that homebrew tool again on an SQL Server database.



  • @Medinoc said:

     In fact, if anyone tells me where the means to export as .sql file is in SQL Server Management Studio, I'll apologise for wasting your time, take back anything I said about Microsoft and escaping, and never use that homebrew tool again on an SQL Server database.

    Right-click on database, Tasks > Generate Scripts...

    You're welcome.



  • It's built into MS-SQL 2008 and later as "Generate Scripts". For 2005, you want the Database Publishing Wizard, free download from Microsoft.



  • @cdosrun said:

    It's built into MS-SQL 2008 and later as "Generate Scripts". For 2005, you want the Database Publishing Wizard, free download from Microsoft.
    I was sure that SSMS 2005 had this as well, albeit with a slightly different (read worse) UI...



  •  Thank you very much. I apologise for wasting your time, SQL Server doesn't need to provide escaping functions, blah blah blah.



  • @Medinoc said:

    In fact, if anyone tells me where the means to export as .sql file is in SQL Server Management Studio, I'll apologise for wasting your time, take back anything I said about Microsoft and escaping, and never use that homebrew tool again on an SQL Server database.

    Damnit, those bastards all stole my glory while I was on the bus.

    BUT I'M GOING TO POST IT ANYWAY!

    It's in "Generate Scripts" numbnuts. There! I feel better! Refreshed!



  • @C-Octothorpe said:

    @cdosrun said:
    It's built into MS-SQL 2008 and later as "Generate Scripts". For 2005, you want the Database Publishing Wizard, free download from Microsoft.
    I was sure that SSMS 2005 had this as well, albeit with a slightly different (read worse) UI...

    To be honest, it's been a long time since I was on 2005. It's possible there was another way to do it, but the Database Publishing Wizard was what I always used.


  • Trolleybus Mechanic

    @Medinoc said:

    Parameterized in, parameterized out. And it won't give you a script that set the parameters.
     

    This may not be what you're looking for, and I may be doing it wrong, and if you're c# app is passing "SET @someparam = ?"...

    Here's what I use to take my sql string and a list of OleDBParameter, and show me what it will look like "post format". Note, don't do this, the run the resulting SQL, because if you do you're a fucking idiot and anything that happens to you is your own fault:


          public static string DebugCommand(string strSql, List<OleDbParameter> olParams, bool blnReplaceBreakWithBR)

          {

                int iPlace = 0;

                string quote;

                string ReplaceText;

     

     

                foreach (OleDbParameter olParam in olParams)

                {

                      switch (olParam.OleDbType)

                      {

                            case OleDbType.BigInt:

                            case OleDbType.Binary:

                            case OleDbType.Boolean:

                            case OleDbType.Currency:

                            case OleDbType.Decimal:

                            case OleDbType.Double:

                            case OleDbType.Integer:

                            case OleDbType.Numeric:

                            case OleDbType.SmallInt:

                            case OleDbType.TinyInt:

                            case OleDbType.UnsignedBigInt:

                            case OleDbType.UnsignedInt:

                            case OleDbType.UnsignedSmallInt:

                            case OleDbType.UnsignedTinyInt:

                            case OleDbType.VarNumeric:

                                  quote = "";

                                  break;

                            default:

                                  quote = "'";

                                  break;

                      }

     

                      iPlace = strSql.IndexOf("?", iPlace);

                      if (olParam.Value is DBNull)

                      {

                            quote = String.Empty;

                      }

     

                      ReplaceText = String.Format("{0}{1}{0}", quote, (olParam.Value is DBNull ? "null" : olParam.Value.ToString()));

     

                      strSql = strSql.Remove(iPlace, 1).Insert(iPlace, ReplaceText);

     

                      // Move beyond the inserted text, since if it was a string that contained a ?, it would gobble up a placeholder

                      iPlace = iPlace + ReplaceText.Length;

                }

     

                if (blnReplaceBreakWithBR)

                {

                      strSql = strSql.Replace("\n", "<br />");

                }

     

                return strSql;

          }




  •  ^Note that your debug function doesn't do escaping either.

    Nowadays I use SQL Server Management Studio's script generator anyway. But I did do a test so see what it generates with every possible UCS-2 character: Turns out it only escapes single quotes, everything else is just pasted into the script as-is.


Log in to reply