-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

Improve this question
1

2 Answers 2

Reset to default
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.

Improve this answer
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...

Improve this answer
10
  • Security issues are addressed by using prepared statements. 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
    Commented Dec 11, 2018 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?
    – Greg Burghardt
    Commented Dec 11, 2018 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
    Commented Dec 11, 2018 at 19:38
  • Out of curiosity, what other kinds of remote code execution attacks are possible?
    – Greg Burghardt
    Commented Dec 11, 2018 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
    Commented Dec 11, 2018 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.