Timothy Fisher Timothy Fisher - 4 months ago 9
PHP Question

How to pass query through an Ajax request

I'm working on paginating some data with Ajax requests. When one of the page number buttons is pressed it will send a request to a separate file to generate the next page in a table.

On my main page I'll have something like:

$query = "Select * from table WHERE field = 'something' LIMIT 5";
$result = mysqli_query($con, $query);
$row = mysqli_fetch_assoc($result);

// dump results as table


When I write the script to create a new xmlhttp request object to my "paginate.php" file, how can I carry this same query over to the file since it may dynamically change based on user input?

I was thinking of just passing the whole query string as a function parameter via a POST request, but am wondering if there is a more efficient way of doing this.

Answer

I was thinking of just passing the whole query string as a function parameter via a POST request

Definitely do not do this! It's really really bad security practice to let the browser (ie. user) run queries directly against your database. I made this mistake in early days and my site got 0wned in no time.

Your PHP file should accept parameters, validate them, then use them to run the query

1. You XHR object could sends: page_number=5

2. Your PHP validates the input and dynamically builds the query:

//set page to 1 if none was provided. 
$pg = isset($_POST['page_number'])? (int)$_POST['page_number']: 1;
$pg = max(1,$pg); // lowest allowed pg number is 1

Once you have the page number, and you are sure it's an integer (not some nefarious SQL command that a user sent to your server), you can use it in your query:

$size = 5; //# of results per page
$start = ($pg-1) * 5;   
$query = "SELECT * from myTable WHERE field='something' LIMIT $start,$size";

Note that if the field value something comes from the user, you don't want to include it in the query directly (this goes for any user-supplied value). Instead, you should use prepared statements and parameterized queries

Resource: https://www.owasp.org/index.php/SQL_Injection