0

I'm working on a form to add data to a Mysql database. First, I well mention I am adding rows to multiple tables witch I don't think is the problem, but I have no Idea at this point. basically, when I submit the form, it adds a row to Locations and Contributors table. however, when I check the database to see if it goes throw (I get now errors on my php page) there is columns missing data. I think I may have got my variables wrong, but I have looked over them many times and see no errors. However, that's the only resign I can think that it would add blank columns in the row.

The two functions to add data.

AddContributer($FirstName, $LastName, $OrgName);
AddLocation($Room, $Shelf, $Row, $Box, $Display, $Missing);

function AddContributer($FirstName, $LastName, $OrgName) {
    include("DBConection.php");
    $sql = "INSERT INTO `Contributor` (`Contributor ID`, `FirstName`, `LastName`, `Organization`) VALUES (NULL, '$FirstName', '$LastName', '$OrgName')";
    if ($conn->query($sql) === TRUE) {
        echo "New contributer created successfully"; 
    } else {
        echo "Error: " . $sql . "<br> Can not add contributer data";
    }
}

function AddLocation($Room, $Shelf, $Row, $Box, $Display, $Missing)
{
    include("DBConection.php");
    $sql = "INSERT INTO `Location` (`Location ID`, `Room#`, `Shelf#`, `Row#`, `Box`, `Missing`, `OnDisplay`) VALUES (NULL, '$Room', '$Shelf', '$Row', '$Box', '$Missing', '$Display')";
    if ($conn->query($sql) === TRUE) {
        echo "New Location created successfully";
    } else {
        echo "Error: " . $sql . "<br> Can not add Location data";
    }
}

Snippets of my code to get data that dose not show on sql.

$Room = $_POST["Room#"];
if($Room == "Item Room"){
    $Room = "Unknown";
}
$Shelf = $_POST["Shelf#"];
if($Shelf == "Item Shelf"){
    $Shelf = "Unknown";
}
$Row = $_POST["Row#"];
if($Row == "Item Row"){
    $Row = "Unknown";
}
$Box = $_POST["Box"];

Picture of data base rows. (It only happens to the location table) enter image description here enter image description here

Some Other important info: Room#, Shelf#, and Row# are all select options in the database. Box is a text option with the same format as the contributor rows.

Sorry for the long post when I probably just spelled something wrong. :)

Barmar
  • 741,623
  • 53
  • 500
  • 612
Liam Morton
  • 67
  • 6
  • 2
    Your script is vulnerable to [SQL Injection Attack](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even if [you are escaping variables, its not safe](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string%5D)! You should always use [prepared statements and parameterized queries](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either MYSQLI or PDO instead of concatenating variables into the query. – Barmar Jun 15 '23 at 16:36
  • How about turning error reporting on! Have you? – RiggsFolly Jun 15 '23 at 16:38
  • 1
    Putting `#` symbols into a column name is a very bad and totally unnecessary thing to do – RiggsFolly Jun 15 '23 at 16:39
  • Have you tried printing the values of all the variables `$Room`, `$Shelf`, etc.? – Barmar Jun 15 '23 at 16:40
  • For a particular 'missing' column (for example, `Row#`), have you confirmed that you have a value for the PHP variable `$Row`? If not, I suggest you do `var_dump($Row);` just before you execute your SQL statement, just to confirm that it has the value you think it should have – Rob Eyre Jun 15 '23 at 16:41
  • Thaks for the extra tips ill look into all of them. I will try displaying the data be for it goes in the database and see what happens. – Liam Morton Jun 15 '23 at 16:42
  • 1
    Or `var_dump($sql)` to see the whole thing. – Barmar Jun 15 '23 at 16:42
  • Thanks, it is now working. I was right about the simple mistake :). however, the extra tips are very helpful and will indeed update my variables and make the code more secure. – Liam Morton Jun 15 '23 at 16:53
  • @RiggsFolly I would never personally put a `#` symbol in a column name, but I'm curious why you consider it "**very bad**"? – GrumpyCrouton Jun 15 '23 at 17:47
  • I don't speak for Riggs, but my assumption is that it is just asking for possible trouble later because you will be forced to always escape that column name. Further, `#` is a comment in some systems (I think MySQL, specifically) and you could trip up some things. Image [this](http://sqlfiddle.com/#!9/e9031e/1) for fun! Granted, some people might argue that escaping columns is a best practice, so from that perspective the issue is moot. – Chris Haas Jun 15 '23 at 18:01
  • You are open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) and [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even data from the database, [you are still at risk of corrupting your data](https://bobby-tables.com/). If this is a school project. Best time to learn. How to do things right. – Jason K Jun 15 '23 at 20:02
  • 1
    @GrumpyCrouton Well basically because it is so unnecessary – RiggsFolly Jun 15 '23 at 22:47

0 Answers0