很多空白区进入数据库...（PHP，Mysql）

Question

Good day, First off I'm an I.T starting to get into coding, so please be gentle ^^.

So currently I'm building a script where it takes input (Nexmo) and sends it to my database.

Sadly LOTS of blank spaces seem to be filling up my database. For some reason I can't seem to be able to filter out these nulls?

Would anyone be willing to help? Thanks! Heres the code

require('db.php');
if (!$connect) {
    die('Could not connect: ' .mysql_error());
}
$sql = "INSERT INTO `INCOMING` (`TO`, `TEXT` , `FROM`, `MESSAGEID`) VALUES ('$_GET[to]','$_GET[text]' ,'$_GET[msisdn]' , '$_GET[messageId]')";
if (!mysql_query($sql)){
    die('Error: ' . mysql_error());
}
else  {

    echo "TO";
}


mysql_close();

Answer 1

Welcome to StackOverflow and to programming!

First of all, I get that you're learning but I'm going to be hard on you to help you. You've got a bad habit from a book or tutorial and I'm not going to let you learn it.

SQL Injection is the #1 vulnerability on OWASP. By inserting data directly from the request (i.e., $_GET) into a SQL query, you are exposing this vulnerability. At the very minimum, escape the data first before integrating with the query

$to = mysql_real_escape_string($_GET['to']);
$text = mysql_real_escape_string($_GET['text']);
$msisdn = mysql_real_escape_string($_GET['msisdn']);
$messageId = mysql_real_escape_string($_GET['messageId']);

$sql = "INSERT INTO `INCOMING` (`TO`, `TEXT` , `FROM`, `MESSAGEID`) VALUES ('$to','$text' ,'$msisdn' , '$messageId')";

I know it looks like more work but good programming is not about saving yourself keystrokes. That being said, you're much better off switching to PDO and using parameterized queries. Besides, the mysql_* family of functions are deprecated.

Now - on to your actual problem. All incoming data needs to be validated and oftentimes filtered to match the business rules of your application. For example, if you want to require that all of these fields have at least one non-whitespace character, you'll need to enforce that. Building off the snippet from above:

function filterInput($value)
{
    // Trim all leading/trailing whitespace and newlines
    return preg_replace('/^[\s
]+|[\s
]+$/', $value);
}

function validateInput($value)
{
    if (NULL === $value || '' === $value) {
        return false;
    }

    return true;
}

// Filter input per your business rules
$to = filterInput($_GET['to']);
$text = filterInput($_GET['text']);
$msisdn = filterInput($_GET['msisdn']);
$messageId = filterInput($_GET['messageId']);

// Now verify they all have real values
if (validateInput($to) && validateInput($text) && validateInput($msisdn) && validateInput($messageId)) {
    // Now escape for SQL
    $to = mysql_real_escape_string($to);
    $text = mysql_real_escape_string($text);
    $msisdn = mysql_real_escape_string($msisdn);
    $messageId = mysql_real_escape_string($messageId);

    // Proceed with INSERT
}

And yet again, I'm sure you're thinking "holy cow that looks like a lot of work" because I took one line of your code and turned it into 20, but that's part of the deal with programming. It's also why people spend lots and lots of time writing libraries and frameworks to abstract-away a lot of this type of "grunt work" code.

Happy coding!