I'm using an ajax call to query a database. I'd like to pass a complex sql query as part of the ajax data. Is this the way I should be doing it?
var myQuery = 'select * from table....';
$.ajax({
type: "GET",
url: 'jsonQuery.php',
dataType: 'json',
data: {keyvalue: 2416, q: myQuery},
success: function(pieData) {
//do something with the response
}
});
Yes, there is a better way. Keep the query on the .php page and send a post type which tells you which one to use.
Example:
data: {keyvalue: 2416, q: 2},
Then you take your query that corresponds to number 2 and use that! No need to pass the SQL along!
As these others say, absolutely don't send straight SQL in the AJAX call. A hacker could easily write their own SQL query to execute whatever code they want to on your database. Insead, you can pass through POST several different field values that you'd like to filter by (for example, a "name" value or "key" or "age_range"). Then, set up the PHP on the receiving end to be smart about when to use these values; if key is present, use that as the identifier and use query X. If key is absent, check for name or other values to perform a search for the right row, and plug them into query Y.
As IngodItrust says, you can also send a POST value that specifies which query to use, ie
q: 'LongerQuery'
then in the receiving PHP, have several IF or CASE statements which prepare a different query depending on which Q value was present.
My site has a chart generator where a user can change settings for what data goes on the X and Y axes, whether the data are split into different series, and how the data pool should be filtered down if the user only wants to look at a specific demographic. These settings are sent through AJAX/POST when the user clicks a "Generate" button. The receiving PHP page constructs the chart data query based on these 20ish inputs; the resulting queries can look quite quite different depending on the settings the user chose. I'm describing this to illustrate that AJAX can be used to build some extremely flexible and user-responsive queries, without creating a security risk.
You should not actually pass any SQL to jsonQuery.php directly, if that is what I understand you to mean. To do this will open you up to SQL injection attacks. See http://bobby-tables.com/ of just google the term.
Rather, pass parameters to your script, for example jsonQuery.php?do=SEARCH&itemID=12&type=RED_ONES and create your query within jsonQuery.php using those parameters. Chack each for expected values before though..
