0

I've recently had an exam about a simple ERP system with CRUD functionality, and the SQL queries had to be in a separate folder only containing SQL files (exam requirement). This was the first time I encountered this form of doing SQL calls, I usually just write the query-strings directly in the code. This made me wonder if there was some form of security, separating the files? What is the general way of doing this, and would it be language specific what to choose? The exam was in C#, but I'm currently working on a user system in Python.

Sp00kyRe4l1ty
  • 13
  • 5
  • 1
    Having the SQL-code in one folder makes it easier to make changes when database structure changes, or to find out if a field is used. When putting the scripts in one folder also your DBA can check these things, and your DBA need not have Python (or C#) knowledge. – Luuk Apr 22 '22 at 09:40
  • 1
    I cannot answer this question, as I don't know what is best practise here. I, too, have the queries directly in my code usually. But when it is a about a process, say a payment must be stored in the transactions table, but then there must be an update in the payments_due table, too, we use stored procedures instead. The actual SQL statements are thus stored in the database. (If you want to, you can put all your SQL code there.) This is especially recommended when you are using a DBMS that features a programming language, as Oracle and PostgreSQL do. – Thorsten Kettner Apr 22 '22 at 09:43
  • In a larger project your SQL code is completely seperate to your application code, and hard coded SQL strings inside your app are frowned upon – Nick.Mc Apr 22 '22 at 09:43
  • @Nick.McDermaid I usually write methods for each query, passing parameters such as selectors, table-name, etc. To make it dynamic, Is it still bad practice? – Sp00kyRe4l1ty Apr 22 '22 at 09:49
  • 1
    This is probably organization-by-organization. In a larger project a web developer will likely (hopefully?) be working with some kind of ORM that abstracts the database queries themselves away from the code calls that generates these queries. When you need something db-specific like a migration that adds a stored procedure, those likely do belong in their own file. – nzifnab Apr 22 '22 at 09:50
  • 1
    @Rosenkilde95 That seems generally fine? As long as you're escaping any potential user input of course. Again, this is completely up to the code styles and standards of the project, organization, or instructor you're working on/with. If the instructor wants queries in a separate file, do that, and have your code call out to those. BUT ALWAYS SANITIZE USER INPUT. ALWAYS ALWAYS. This is the easiest thing to forget for new devs. – nzifnab Apr 22 '22 at 09:53
  • I don't think there is a best practice. It depends on the project, the organization and the individual developer(s) – HoneyBadger Apr 22 '22 at 10:48

0 Answers0