-4

I have a MySQL database and I am working with Core PHP.

What is the best practice in terms of accessing the database? Should I

  1. create functions in MySQL and access them in my PHP-code OR
  2. write inline queries from code directly?

Let me know if you need any additional info

maxkoryukov
  • 183
  • 6
Mohd Maaz
  • 101
  • 2
  • This should actually be a duplicate of [How much business logic should the database implement?](https://softwareengineering.stackexchange.com/q/194446/118878). – Greg Burghardt Dec 11 '18 at 16:12

2 Answers2

1

The essential factor in your calculation is scalability.

You can scale your database by creating a cluster or splitting out the queries from the commands.

You can scale your webserver by spinning up more instances and load balancing across them.

Usually it's much cheaper and easier to scale your webserver.

Therefore where you have a choice of where to run a function, try to run it on your webserver rather than the database.

Also, there is the question of how you deploy changes. Usually it's much easier to have two versions of your website than two versions of your database.

Therefore where you have business logic which is likely to change. Prefer putting it in your code.

Ewan
  • 70,664
  • 5
  • 76
  • 161
0

By default, you should actually prefer to use SQL stored procedures because of a laundry list of performance improvements the RDBMS can give you. You also win in security by removing direct table access and allowing access only in very controlled ways.

You also get to write less PHP code, which is a boon in itself...

Alex Reinking
  • 1,607
  • 11
  • 16
  • Security issues are addressed by using [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Business logic changes often, and putting that logic in a technology that was originally meant to store data is a real pain to develop and test, especially if you need to interact with resources outside the database. – Greg Burghardt Dec 11 '18 at 16:07
  • However, there are cases where stored procedures run much faster, and are a better fit, despite being harder to develop and test. It really depends on the problem being solved. A very related question: [How much business logic should the database implement?](https://softwareengineering.stackexchange.com/q/194446/118878) – Greg Burghardt Dec 11 '18 at 16:09
  • @GregBurghardt if you use prepared statements, then you protect yourself against one class of SQL injection attacks. But that doesn't protect against other kinds of RCE vulnerabilities. If you disable direct table access, then an attacker has to learn much more about your system to pull off an attack. I don't buy the "originally meant to store data" argument. Modern RDBMSes are designed for much more than SELECT-FROM-WHERE – Alex Reinking Dec 11 '18 at 19:38
  • Out of curiosity, what other kinds of remote code execution attacks are possible? – Greg Burghardt Dec 11 '18 at 19:45
  • A common one is where an attacker abuses a file upload script to inject php into the site. Any other application specific exploit that allows overwriting a file would do the trick, too. – Alex Reinking Dec 11 '18 at 19:49
  • Not seeing how the file upload script could be used in conjunction with a prepared statement to compromise the database or the web server... If you use the correct methodology you can properly guard against these attacks *and* still use prepared statements. – Greg Burghardt Dec 11 '18 at 20:43
  • A file upload that allows an attacker to run arbitrary SQL will not be protected by prepared statements. SP will frustrate such an attack by requiring the attacker to learn how to (ab)use the SPs – Alex Reinking Dec 11 '18 at 20:47
  • So don't run SQL that was uploaded in a file. – Greg Burghardt Dec 11 '18 at 20:51
  • "So don't write code that has vulnerabilities" – Alex Reinking Dec 11 '18 at 20:59
  • surely you can still use sprocs AND have calculations in php? – Ewan Dec 11 '18 at 22:00