How to pass SQL query to PHP script?

The site has a MySQL database. It is necessary to output data with pagination.

  1. A SQL query is built based on filling in the search form.
  2. Display the first 20 entries.
  3. Using the LIMIT parameter, we break the request into pages and make links to go to other pages…

Question: what to do with the received request, i.e. whether to pass it as a parameter via GET or via POST, create a global variable or write it to the database, or rebuild it on each next page, passing all the parameters received in the search form there? How to do it better in terms of speed and reliability from SQL injections?


Answer 1, authority 100%

The practice is as follows – only the page number is transmitted in GET / POST, then LIMIT is calculated by the number & OFFSET and a new request is generated on each page.
If the request is heavy, i.e. takes a long time, then you can cache it. There are several approaches here.

For example, one of them:

take 1000 query lines and put it in a temporary file, then put the file name in the cache, and data from pages 1-50 / 20 lines per page use this file. Well, if the user is very stubborn and 1000 search results are not enough for him (usually 10 pages calm down after the first view), then you will have to take the next 1000 lines, etc.

disadvantages: you need to make a cache invalidator, an external script that runs by cron and destroys all old files (which have a creation time of more than 30 minutes after the current one).
I ran such a cache cleaner once a day at night.

as an option, we use memcache, but here part of the time will be eaten by the serialization of a large array, although this is a drop in the ocean. there is no need to think about invalidation, memkesh will do everything for you.

Some use the database as a cache – stupid in my opinion.


Answer 2, authority 50%

You can write a filter to a session and then only change the page.

Accordingly, from the pros – the data is transmitted once, + you can continue the search at any time until the browser is closed. It is advisable to take into account the reset button and the formation of the filter in the session should be carried out among the first operations.

I don’t see any cons yet, except that it will be problematic to transfer the link, but it can be formed from the same filter and posted separately.


Answer 3

The most secure way to pass all the necessary parameters from the filter form to the server. And the request itself is generated and executed on the server. Otherwise, you will create a serious vulnerability.


Answer 4

How will you pass the get or the post does not matter, as I understand it, only the digital value for the limit will be transmitted, then frame your variable with the intval () function, this will save you from injection, for other values ​​​​(not numbers) I advise you to use mysql_real_escape_string ( ).

Here we also talked about the filter form above, it has the right to life only as additions to my words above, it will not be difficult for a hacker to fake the information sent by the form.