API不允许使用文本

Question

I've just created a simple API for a CAD/MDT I'm working on, I've managed to get it to show the correct information when I do /citations/userid/1. This will then display all the correct values from the SQL database however, if I do /citations/issued_by/kevingorman1000 it will just throw an error. I can't tell what the error is as I'm using Slim php and can't seem to get the errors to display.

Any ideas why it isn't working ? I've added my code below..

$app->get('/citation/issuedby/{issued_by}', function(Request $request, Response $response){

$issued_by = $request->getAttribute('issued_by');

$sql = "SELECT * FROM ncic_citations WHERE issuedby = $issuedby";
try{

    // Get DB Object
    $db = new db();
    // Call Connection to DB
    $db = $db->connect();

    $stmt = $db->query($sql);

    $issby = $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;

    echo json_encode($issby);

} catch(PDOExecption $e) {
    echo '{"error"} : {"text": '.$e->getMessage().'}';
}});

Any ideas why this is the case? Does it only allow getting via number or do I need too do something else? First time using this and kinda new to PHP as well.

Thanks for any help.

Answer 1

Your problem is called SQL injection. You can solve it by using prepared statements. Never escape the values with quotes or anything else, as others might have suggested.

$sql = "SELECT * FROM ncic_citations WHERE issuedby = ? ";
$stmt = $db->prepare($sql);
$stmt->execute([$issuedby]);
$issby = $stmt->fetchAll(PDO::FETCH_OBJ);

For a good tutorial on PDO and prepared statements I recommend: https://phpdelusions.net/pdo