php服务使用jquery ajax执行sql命令

Question

<?php

header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json'); 

$mysql = mysql_connect('corte.no-ip.org', 'hostcorte', 'xxxx');
mysql_select_db('fotosida');

if((isset($_POST['GetPersons'])))
{

if(isset($_POST['ID'])) {
    $query = sprintf("SELECT * FROM persons WHERE id='%s'",
        mysql_real_escape_string($_POST['ID']));
} else {
    $query = "SELECT * FROM persons";
}

$res = mysql_query($query);

while ($row = mysql_fetch_assoc($res)) {

    for ($i=0; $i < mysql_num_fields($res); $i++) {
        $info = mysql_fetch_field($res, $i);
        $type = $info->type;

        if ($type == 'real')
            $row[$info->name] = doubleval($row[$info->name]);

        if ($type == 'int')
            $row[$info->name] = intval($row[$info->name]);
    }

    $rows[] = $row;
}

echo json_encode($rows);


}

mysql_close($mysql);
?>

This works ok for generating a json object based on a database query. Im not very familiar with PHP, so i would like some feedback from you before i proceed with this. Is this a good way of calling the database using ajax? Other alternatives? Frameworks maybe?Are there any security problems when passing database queries like UPDATE, INSERT, SELECT etc using an ajax HTTPPOST? Thanks

Answer 1

To simplify CRUD operations definitely give REST a read.

As mentioned, stop using the @ (AKA "shut-up") operator in favor of more robust validation:

if(isset($_GET['key'])){
    $value = $_GET['key'];
}  

Or some such equivalent.

Using JavaScript/AJAX, aggregate and send your request data, such as IDs and other parameters, from the form fields into a JSON object. Not the built query. The only time the client should be allowed to manipulate directly executed SQL is if you're creating an web based SQL client. Architect your URLs meaninfully (RESTful URLs) so that your HTTP request can be formed as:

GET users/?id=123

DELETE photos/?id=456

Or alternatively:

GET users/?id=123

GET photos/?method=delete&id=456

Server-side, you're going to receive these requests and based on parameters from the session, the request, etc., you can proceed by firing parametrized queries:

switch($method){
    case 'get':
        $sql = 'SELECT * FROM `my_table` WHERE `id` = :id';
        break;
    case 'delete':
        $sql = 'DELETE FROM `my_table` WHERE `id` = :id';
        break;
    default:
        // unsupported
}
// interpolate data from $_GET['id'] and fire using your preferred
// database API, I suggest the PDO wrapper.

_{See PDO}

Generate output as necessary, and output. Capture on client-side and display.

Always validate and filter user input. Never send and execute raw SQL queries, or concatenate raw user input into SQL queries.

---

With regard to your question, here's a possible snippet:

(Note -- I haven't tested it, nor rigorously reviewed it, but it should still serve as a guide -- there is a lot of room for improvement, such as refactoring much of this logic into reusable parts; functions, classes, includes, etc.)

header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');

$error = array();

// get action parameter, or use default
if(empty($_POST['action']))
{
    $action = 'default_action';
}
else
{
    $action = $_POST['action'];
}

// try to connect, on failure push to error
try
{
    $pdo = new PDO('mysql:dbname=fotosida;host=corte.no-ip.org', 'hostcorte', 'xxxx');
}
catch(Exception $exception)
{
    $error[] = 'Error: Could not connect to database.';
}

// if no errors, then check action against supported
if(empty($error))
{
    switch($action)
    {
        // get_persons action
        case 'get_persons':
            try
            {
                if(!isset($_POST['id']))
                {
                    $sql = 'SELECT * FROM `persons`';
                    $stm = $pdo->prepare($sql);
                    $stm->execute();
                }
                else
                {
                    $sql = 'SELECT * FROM `persons` WHERE `id` = :id';
                    $stm = $pdo->prepare($sql);
                    $stm->execute(array(
                        'id' => (int) $_POST['id'],
                    ));
                }
                $rows = array();
                foreach($stm->fetchAll() as $row)
                {
                    $rows[] = $row;
                }
            }
            catch(Exception $exception)
            {
                $error[] = 'Error: ' . $exception->getMessage();
            }
            break;

        // more actions
        case 'some_other_action':
            // ...
            break;

        // unsupported action
        default:
            $error[] = 'Error: Unsupported action';
            break;
    }
}

// if errors not empty, dump errors
if(!empty($error))
{
    exit(json_encode($error));
}

// otherwise, dump data
if(!empty($rows))
{
    exit(json_encode($rows));
}