Saving (with JS Request) and reading (with PHP) XML in MySQL

Question

I'm trying to save some xml content (that I receive as plain text) into my site's database. I read about saving XML content and someone suggested it is not a good idea to save XML in a text field (database), so I decided to do it in a blob. The thing is I'm doing it via CORS, through javascript this way:

var formData = new FormData();
formData.append("name", 'myNewFile'); 

// THE XML CONTENT
var content = '<a id="a"><b id="b">hey!</b></a>'; 
var blob = new Blob([content], { type: "text/xml"});
formData.append("file", blob);

var request = new XMLHttpRequest();
request.open("POST", url);
request.onreadystatechange = function() {
    if(request.readyState == 4 && request.status == 200) {
        resultsContainer.innerHTML = (request.responseText );
    }
}
request.send(formData);

On the server, I store it with:

$name = $_POST['name'];
$file = $_POST['file'];

$sql = "INSERT INTO ProfileFiles (name, file)
VALUES ('$name', '$file')";

It seemed to work, the entry was created in the database but I can't see what's inside the BLOB field. So, I tried to read that from server, using PHP, but I'm retrieving just "0" in the file field.

$sql = "SELECT datetime, name, file FROM ProfileFiles";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Timestamp: " . $row["datetime"]."<br>";
        echo "Name: " . $row["name"]. "<br>";
        echo "Content: " + $row["file"];
        echo "<br>----------<br>";
    }
}
else
{
    echo "Nothing";
}

What am I missing? Thanks in advance! I never worked with PHP.

It's not recommended to save xml in a blob for the same reason it is not recommended to save in a text field: XML is carrying information in a structured form, but saving it in a flat field does not reflect its structure at all. — Lorenz Meyer
– Lorenz Meyer, Commented Jun 4, 2015 at 16:54
The way you're storing the data is not correct. You're not escaping the values. A prepared statement would be better. — Lorenz Meyer
– Lorenz Meyer, Commented Jun 4, 2015 at 16:56
Hi Lorenz! Thank you for repliying, but I don't get your point. What do you suggest? To save it as a plain text? Am I going to have problems by doing this? — laradover
– laradover, Commented Jun 4, 2015 at 16:57
"You're not escaping the values". From client side? In the JS code? — laradover
– laradover, Commented Jun 4, 2015 at 16:58
About storing XML in a database field : you will not run in any problem, but it is just a flat blob to the database. You will not be able to treat the data contained in the XML file. The only thing you can do, is reading the XML file out of the database. — Lorenz Meyer
– Lorenz Meyer, Commented Jun 4, 2015 at 17:40

Lorenz Meyer · Accepted Answer · 2015-06-04 20:42:46Z

1

The reason why you don't get anything in $_POST['file'], is that you are sending it as a file. Files that are posted are in the superglobal variable $_FILES not $_POST. $_FILES['file'] will contain an array

array('name' => '...', 'tmp_name' => '...', 'type' => '...', 'size' => '...');

The content will be saved to a temporary file whose name is stored in $_FILES['file']['tmp_name']

You see, you really go astray here... What you have to do is to send the XML data as a POST variable and not a file. When doing this, you can save the data to the database like you tried it, but with prepared statements, it will be something like (assuming you are using mysqli

$name = $_POST['name'];
$file = $_POST['file'];

$sql = "INSERT INTO ProfileFiles (name, file)
VALUES (?, ?)";
$stmt =  $mysqli->stmt_init();
$stmt->prepare($sql);
$stmt->bind_param("ss", $name, $file);
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();

The point of using a prepared statement is this :

If the file contains a ', you get an error in the query. Also your code is vulnerable to sql injection. You need to escape the strings in the query.

I never used mysqli myself, and the code I gave looks a bit clumsy, so here's an alternative :

$sql = "INSERT INTO ProfileFiles (name, file)
VALUES ('". mysqli_real_escape_string($name)."', '".mysqli_real_escape_string($file) ."')";

edited Jun 4, 2015 at 20:42

answered Jun 4, 2015 at 17:09

Lorenz Meyer

20.1k23 gold badges83 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

laradover Over a year ago

Hey Lorenz! Thank you! But it just saves me ? and ? values :(

laradover Over a year ago

VALUES (?, ?) must be without '

Collectives™ on Stack Overflow

Saving (with JS Request) and reading (with PHP) XML in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related