• Pjrz (unregistered)

    That's great. It allows anyone to change any SQL used by the program to anything they like. Added flexibility. What could possibly be wrong with that?

  • bvs23bkv33 (unregistered)

    when Russians stolen Diablo they changed all strings to Russian anyway, the fact that strings were stored in compiled executable didn't stop them

  • Gargravarr (unregistered)

    Well, in fairness, at /least/ they're being used in prepared statements... and they're all in one file...

    This is how low my expectations have been brought through years following this site.

  • Martin Milan (google)

    Drop table hope;

  • Martin Milan (google)

    Drop table hope;

  • RLB (unregistered) in reply to Martin Milan

    Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS) Can't drop database 'hope'; database doesn't exist

  • I'm not a robot (unregistered)

    Still better than forcing everyone to use stored procedures, and having to grovel to the DBA whenever you want a slightly different query.

  • djingis1 (unregistered) in reply to Gargravarr

    Not just from following this site. From working with other, often (very) experienced developers, too.

  • Mike (unregistered)

    Far better than in-lining them, imho

    Also means at some point they can replace the sql lookup implementation without having to change all the calling code

  • Robert Moskal (unregistered)

    It actually doesn't seem like a terrible idea inside of a code base used by a single team.

  • (nodebb) in reply to RLB

    Well, there's your problem. (Where did Adam quote that from anyway?) He was dropping a table; you tried dropping the entire database.

  • (nodebb)

    So? Now just write a DSL to allow users to write their own queries without actually knowing SQL.

  • NoCrystal (unregistered) in reply to nabru

    "Now just write a DSL to allow users to write their own queries without actually knowing SQL." Oh, that sounds so much like the old "let the users define custom reports" that I used to hear. There's a reason I was paid the mediocre bucks to write optimized-ish SQL for those reports I very carefully designed....

  • Kashim (unregistered) in reply to Martin Milan

    DELETE FROM Hope WHERE Person='Ye' AND Action='Entering' AND Location='Here';

  • dpm (unregistered)

    Still a better love story than Twilight.

  • Jim B (unregistered) in reply to I'm not a robot

    I've been in shops where everything (repeat everything) had to be in a stored procedure.

    I'm currently working on stuff that uses Entity Framework code-first where the database structure is defined in C# code (a table for each interface and resulting model). The guy that first designed this is a brilliant front end developer for imaging software. He chose EF code-first as he knew nothing about data design. The resulting database code shows this.

    So the best of both - let the developers design their database. Use an ORM for access. Use the DBA for their expertise.

  • Yazeran (unregistered) in reply to bvs23bkv33

    Well as long as the replacement strings had the same length in bytes, replacing them in the executable should not matter or be that hard.

    One of my friends back in university did that with a hex editor and replaced 'Starting MS-DOS' to 'Welcome to hell' (or something similar, can't remember the exact string) in comand.com.

  • Dev (unregistered) in reply to I'm not a robot

    Spoken like a true developer who doesn't understand what stored procedures are or the advantages they have

  • Where's the WTF? (unregistered)

    And apart from storing the unencrypted connect data into the very same file where's the WTF? We don't know if or how this file can be accessed by the end user; it may very well be packed into a .war file for the user unable to see or access. If the Database is accessible to the end user what prevents him of firing up google and looking into the latest security vulnerabilities to gain access to an unpatched database.

    And since everyone's falling into autistic screeching when the word "stored procedure" or "view" is even mentioned I don't believe everyone is wrinkling their nose because the sql statements aren't encapsulated in stored procedures returning ref cursors and recurring SQL Statements are wrapped in views. And most certainly ORM tools aren't the solution to every database problem. Try to tune a SQL Statement generated by one of those monstrosities...but sure enough, that's the DBAs department, right?

    OK, one could argue that a DDL Statement really shouldn't make it into the runtime version of the application, so really, what's so bad here?

    • the developer encapsulated SQL Statements instead of cluttering them all over the place
    • prepared statements are used (big plus)

    I am guessing the database either doesn't support stored procedures, the developer didn't know them, or because a coleague of a coleague of a friend of a cousin of the boss' dog sometime worked in a job where he had to use them and instead of learning how to use them properly the pure horror followed. So stored procedures are plain evil and must not be used at all costs. And in that case having the SQL Statements stored in some named fashion, giving them a unique name sounds like a good idea at least to me.

    cheers

  • Steve (unregistered)

    Sprocs every day of the week!

  • Scott Christian Simmons (google)

    "A simple regex-based parser ..."

    Has a sentence that started that way ever ended well?

  • Redforeman (unregistered)

    1987 called. They want their code back.

  • (nodebb) in reply to nabru

    "So? Now just write a DSL to allow users to write their own queries without actually knowing SQL."

    Welcome to the "Inner platform effect". This has been discussed before. It always yields a giant WTF!

  • I dunno LOL ¯\(°_o)/¯ (unregistered)

    They read the file at startup and stored it in a hash? But what if they wanted to change the strings at runtime? TRWTF is that they didn't make a function that opened sql.txt, read every line until it found the correct item, then returned it. Bonus points if it could rarely leave the file handle open such that the program runs out of file handles and dies with a cryptic error message after running for six hours. Triple word score if it used some kind of eval() on what it read from sql.txt to create a code execution vulnerability.

    Good on them for at least using prepared statements. But they probably only did it because it was too hard to splice parameters into the SQL command strings.

  • o11c (unregistered)

    The only WTF here is cramming each whole SQL statement on a single line. Well, that and using positional rather than named parameters.

    One way or another, keeping all SQL statements in one place makes schema changes a lot easier.

    What I've found is best is putting each statement - or set of statements (for DDL and some explicitly-transactioned DML) - in a separate .sql file (in a single sql/ directory), which (among other things) gives syntax highlighting.

  • gnasher729 (unregistered) in reply to Scott Christian Simmons

    JSON would be standards based, with parsers widely available, and I know I can trust it with quotes, escaped characters etc.

  • Nathan (unregistered) in reply to Dev

    Here here. I'm not a robot is the sorry of developer who should be jailed when their implementation gets hacked.

    Tottally horrified at "I'm not a robot"'s stupidity.

  • Nathan (unregistered)

    This sorry of article should be titled. "How to get compromised through stupidity". Here's what's wrong with the article.

    1. Externalising code to access the DB is:
    • Fragile
    • hackakable (i.e. see Fragile)
    • easy to alter the system by modifying said sql.txt
    • easy to subvert (once nice spot to inject malicious code)
    • unsecure (App provides global security authorisation functions. The DB itself should enforce this, effectivly one account is authorised to do everything.
    1. Thanks for publishing most of your schema. Now a hacker doesn't need to do anything fiddly to begin their sql injection attack.
    2. The advice given in this article would prompt me to reject said developer from any opportunity. IT is hard enough already without stupiduty like this.
  • Little Bobby Tables (unregistered)

    ... and this technique is bad why, exactly?

  • (nodebb) in reply to Kashim

    CREATE TRIGGER on_entering_hell BEFORE INSERT ON hell EXECUTE PROCEDURE abandon_hope();

  • Cynic Missing Tables (unregistered)

    Notice the lack of Indexes.

  • WTFGuy (unregistered)

    Ref folks above defending an [sprocs for everything] policy ...

    The folks complaining about having to use [sprocs for everything] are mostly complaining about the political problem when devs are responsible to create results on a schedule while DBAs are free to say "no" or "too busy to fix" without consequence. Which leads to horrific work-arounds by the devs when the bosses don't care to solve the politics, or worse yet actively abet the politics.

    As with so much of this stuff the deep baseline WTF is human-to-human, not human-to-machine.

  • RLB (unregistered) in reply to Jim B

    I've worked with (and was hired to write the replacement for) a program that did something very much like that... in Clipper. It was an idea both miles ahead and way behind its time.

  • Gumpy Gus (unregistered)

    Meh, I've seen much worse. Like a large book wholesaler, where if you ask in an email for a copy of the database schema, they forward your email to the head IT guy, who takes an hour to respond, as he's typing in the schema, from memory.

  • TrollingMagician (unregistered) in reply to Scott Christian Simmons

    @Scott - I have a worse one : "A simple regex-based HTML parser ..."

    Olig link: https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags/1732454#1732454

  • Glitterati (unregistered)

    Oh yes the developer should have used sprocs and named arguments. Silly developer, bad developer?

    But what if the code was originally developed for Sqlite which doesn't support sprocs or named arguments?

    Oh yeah... Um it's not a bad compromise it's it?

  • death the kid (unregistered) in reply to Yazeran

    i remember doing the same things in HXD hex Editor, replacing the plain text String from byte code and running them; but the problem occurs if compiled code stores more then just Plain text string as if it store the length (if string static) or storing the checksum value type something that i never figured out what were those things (i was a novice back then :D) .

    But the idea there to store string like each query from SQL.txt into a class and serialize it so that it is not directly in plain sight is still a better choice to get those dirty hands off from your code (WTF :D) or storing them into a dedicated DB table where it can be changed easily by some particular DB user having privilege over that is still good even it increase the first time DB access and fetch time but still is a good thing in a sense...

    Storing everything in file-system including db_password is surely a WTF for programmers...

  • (nodebb) in reply to Glitterati

    But what if the code was originally developed for Sqlite which doesn't support sprocs or named arguments?

    It most certainly does support named arguments, but your language interface to it might not. Sprocs are much less useful, as there's no notion of multi-user permissions involved (and that's a consequence of Sqlite definitely not being a multi-user service, by design), but good language interface will handle the necessary caching so it doesn't matter, and the time to recompile the SQL is usually small enough to be irrelevant next to the cost of accessing the data.

  • Shill (unregistered)

    This reminds me of named Hibernate queries back in the days before annotations. But that was clearly better, because they were placed in an XML file.

Leave a comment on “External SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article