Golang persistence: I want the security of prepared statements, but I also want low GC pressure and not having to write boilerplate for nested structures and joins.

<p>Golang persistence: I want the security of prepared statements, but I also want low GC pressure and not having to write boilerplate for nested structures and joins. Is there a well-tested, proven library out there for me? It seems like I have to choose between speed and security.</p> <hr/>**评论：**<br/><br/>kardianos: <pre><p>Write SQL?</p> <p>sqltext := <code> select a.ID, al.Amount from Account a join AccountLine al on al.Account = a.ID where a.ID = :Account and al.Amount &gt; :Thresh </code> rows, err := db.QueryContext(ctx, sqltext, Named(&#34;Account&#34;, 42), Named(&#34;Thresh&#34;, 9000))</p></pre>gernest_: <pre><p>I have been looking for something similar. I have tried a couple of times to build something like this.</p> <p>At the end of the day, object relational mapping in go doesnt get much attention. The solutions we have are in not so good condition.</p> <p>I decided to fork gorm. Took it apart and started to rebuild it. My strategy is, first making it work with a design that will allow me to profile, isolate pain points and improve, the gradually improve the whole thing by reducing allocations, making it fast and making it approachable by developers.</p> <p>I have been making progress, you can join the efforts. There is still a long way to go. here is the link to the project <a href="https://github.com/ngorm/ngorm" rel="nofollow">https://github.com/ngorm/ngorm</a></p></pre>asyncrep: <pre><p>This was just posted recently: <a href="https://github.com/Tebro/gopsql" rel="nofollow">https://github.com/Tebro/gopsql</a> Maybe this is close to what you&#39;re looking for?</p></pre>mixedCase_: <pre><blockquote> <p>I want the security of prepared statements</p> </blockquote> <p>You don&#39;t need prepared statements for that. You need input escaping. Use the API correctly and you&#39;ll get that for free.</p> <blockquote> <p>but I also want low GC pressure</p> </blockquote> <p>If you don&#39;t want to load things into your app&#39;s heap, then do it on SQL.</p> <blockquote> <p>and not having to write boilerplate for nested structures and joins</p> </blockquote> <p>What boilerplate are we talking about here? You can&#39;t escape writing actual code if you want speed. Otherwise you&#39;re using the wrong tool, Go is for robust, performant applications, not for behind-the-scenes magic.</p> <blockquote> <p>Is there a well-tested, proven library out there for me?</p> </blockquote> <p>sqlx is the most popular complement to the standard library. Saves you time while being fast (enough) and simple.</p></pre>stcredzero: <pre><blockquote> <p>You need input escaping.</p> </blockquote> <p>Is input escaping bulletproof? My understanding from the past two decades is that escaping is not completely bulletproof. It&#39;s a blacklist strategy. If the library developers make a mistake implementing it, something goes through. With prepared statements, there is no concern for this at all. </p> <p><a href="https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_4:_Escaping_All_User_Supplied_Input" rel="nofollow">https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_4:_Escaping_All_User_Supplied_Input</a></p> <blockquote> <p>sqlx is the most popular complement to the standard library. Saves you time while being fast (enough) and simple.</p> </blockquote> <p>That&#39;s my current top candidate.</p></pre>mixedCase_: <pre><p>I don&#39;t know where you read that crap but input escaping is literally what prepared statements do, input escaping is the act of turning semantically significant characters into simple text.</p> <p>And as far as prepared statements go, many Go DB drivers use prepared statements in the background for performance reasons anyway.</p></pre>TheMerovius: <pre><blockquote> <p>I don&#39;t know where you read that crap but input escaping is literally what prepared statements do, input escaping is the act of turning semantically significant characters into simple text.</p> </blockquote> <p>No, it really, <em>really</em> isn&#39;t. Prepared statements do <em>not</em> escape the data into text, they encode it in a well-defined and easily machine parseable binary wire-format. Doing robust input escaping is such a complicated question, that every single day there are new security vulnerabilities exposed even in the products of large companies who <em>know</em> how to do them well; whether it&#39;s in SQL, HTML, javascript or shell.</p> <p>Do not believe anyone who claims that prepared statements do not add security, or that escaping would solve this problem. At the base of it, to write an encoder and parser that correctly and with context-awareness writes out data into a programming-language string you have to be really, really clever, whereas to write an encoder and parser that handles &#34;a string is a 4-byte little-endian length followed by that many bytes&#34; incorrectly you have to be pretty darn incompetent.</p></pre>mixedCase_: <pre><blockquote> <p>No, it really, really isn&#39;t.</p> </blockquote> <p>It was an oversimplification, and the transit encoding doesn&#39;t really matter for what ends up in the database as &#34;just text with no meaning&#34;. That&#39;s what I was trying to communicate. Regardless, the extra contextual detail is appreciated.</p></pre>TheMerovius: <pre><p>It was an oversimplification to the point of being fundamentally incorrect, misleading and if anyone would actually adhere to it, they would be worse off personally and would put their users at peril.</p> <p>It was an oversimplification of the order of saying &#34;well, at the end, Heroin and Aspirin are both just painkillers, so if you&#39;re just careful with your dose when you shoot up, why wouldn&#39;t you use Heroin to treat your Headache?&#34;. The two are so different, that the difference isn&#39;t in <em>quantity</em>, it&#39;s in <em>quality</em>.</p> <p>You need to understand, that escaping <em>does not work</em> to prevent SQL injections. Capable and intelligent people have <a href="http://php.net/manual/en/function.mysql-escape-string.php">tried</a> <a href="http://php.net/manual/en/function.mysql-real-escape-string.php">again</a> and <a href="http://php.net/manual/en/mysqli.real-escape-string.php">again</a> and they have still failed. It is very simple: If you accept user-input and use SQL use prepared statements, otherwise you <em>are</em> vulnerable. Security people don&#39;t say that as a joke, or because they find it hilarious to throw stones in your way, they mean it literally.</p></pre>stcredzero: <pre><blockquote> <p>You need to understand, that escaping does not work to prevent SQL injections. Capable and intelligent people have tried again and again and they have still failed.</p> </blockquote> <p>It seems that the Golang community is currently <em>infested</em> with the idea that using prepared statements is <em>wrong</em> and that escaping is the proper way to go. (And also, that generating a SQL string every time and sending it through the parser and optimizer every time is the best you can do, in terms of efficiency!)</p> <p>I say this as an admirer and user of Golang: In terms of critics accusing Golang of being &#34;Blub&#34; or being the &#34;worse is better&#34; successor or PHP, the above is some of the best evidence supporting their case! </p></pre>TheMerovius: <pre><blockquote> <p>I say this as an admirer and user of Golang: In terms of critics accusing Golang of being &#34;Blub&#34; or being the &#34;worse is better&#34; successor or PHP, the above is some of the best evidence supporting their case!</p> </blockquote> <p>I don&#39;t quite understand what you are trying to say. It reads a bit like you misunderstood me to attack PHP here. I wasn&#39;t (that&#39;s why I called them &#34;capable and intelligent people), on the contrary. I was trying to point out, that it&#39;s a difficult problem and that the specific things PHP have tried give a good trace of that.</p> <p>All modern PHP developers have moved to prepared statements, <em>because</em> of the experiences they made.</p></pre>stcredzero: <pre><blockquote> <p>I don&#39;t know where you read that crap</p> </blockquote> <p><a href="https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_4:_Escaping_All_User_Supplied_Input" rel="nofollow">Where I read &#34;that crap.&#34;</a> This was also the &#34;accepted wisdom&#34; from the early days of web apps, since well before &#34;Web 2.0.&#34;</p> <blockquote> <p>but input escaping is literally what prepared statements do</p> </blockquote> <p>If that&#39;s true, there&#39;s still the important difference that I wouldn&#39;t have to interpose boilerplate code on my own for every SQL statement. However, I doubt that this is how Prepared Statements actually work. If I were implementing prepared statements, I wouldn&#39;t use string-generation then re-parse the SQL statement and send it through the SQL optimizer every single time. That would be stupidly sub-optimal. The optimized, parameterized query should exist as some kind of in-memory representation. </p></pre>mixedCase_: <pre><blockquote> <p>This was also the &#34;accepted wisdom&#34; from the early days of web apps</p> </blockquote> <p>Which was almost all of it wrong, even for the very different dev tools and client software we had to work with/for. There&#39;s more knowledge worth rescuing from before the web was a thing to do modern web development than the years we&#39;ve spent developing all those &#34;best practices&#34;, it was a really shitty time for software development.</p> <p>Prepared statements indeed do escape <em>input</em> while they cache the optimized query plan. This is also why I mentioned that many DB drivers use prepared statements in the back, to cache according to the backing DB engine&#39;s capabilities.</p> <p>TL;DR: Feel free to pass user input as parameters to your statements as long as you use good database drivers.</p></pre>metakeule: <pre><p>Use stored procedures, e.g. with postgresql and it&#39;s json support.</p></pre>