Richard Lloyd
asked on
What is the best way to store an array in a database?
Please can someone advise the best way to store an array in a database?
I normally pass arrays via a session cookie, but there are times that I need to store and retrieve an array in a database. I store a serialised version, as below:
When I retrieve the session cookie and apply the $params (usually to a parameterised sql query, the array works.
However when I store it in a database, it works for a time, then after I update the database record, either updating or leaving the array alone, the array gets returns with added slashes,
The working unserialized array looks like this and works:
After any database manipulation, it looks like this and fails:
The code I am using to update the database (SQLserver and PHP with sqlsrv_query) that seems to cause the problem is:
I have tried encoding the serialised array with base64, using "magic_quotes", "htmlspecialchars" etc and nothing seems to work.
Can anyone advise how to correct or a better way to store the array for long term / cross session availability?
I normally pass arrays via a session cookie, but there are times that I need to store and retrieve an array in a database. I store a serialised version, as below:
<?php
$params=array($foo, $bar);
$_SESSION['params']=serialize($params);
?>When I retrieve the session cookie and apply the $params (usually to a parameterised sql query, the array works.
However when I store it in a database, it works for a time, then after I update the database record, either updating or leaving the array alone, the array gets returns with added slashes,
The working unserialized array looks like this and works:
string(181) "array ( 0 => 'WROES', 1 => 'john', 2 => 'john%', 3 => 'baker', 4 => 'baker%', 5 => '', 6 => '%', 7 => '', 8 => '', 9 => '', 10 => '', 11 => '', 12 => '', )"After any database manipulation, it looks like this and fails:
string(181) "array ( 0 => \'WROES\', 1 => \'john\', 2 => \'john%\', 3 => \'baker\', 4 => \'baker%\', 5 => NULL, 6 => \'%\', 7 => NULL, 8 => NULL, 9 => NULL, 10 => NULL, 11 => NULL, 12 => NULL, )"The code I am using to update the database (SQLserver and PHP with sqlsrv_query) that seems to cause the problem is:
if(($_POST['postback']=='update-profile')){
$id=($_POST['id']);
$paramsprofile=$_POST['paramsprofile'];
$profilename=($_POST['profilename']);
$sql="update tblprofiles set profilename=?, sql=?, params=? where id=?";
$params=array($profilename, $sqlprofile, $paramsprofile, $id);
$result=sqlsrv_query($conn, $sql, $params);
}
$id=test($_GET['id']);
$sql="select profilename, profileid, sql, params from tblprofiles where id=?";
$params=array($id);
$result=sqlsrv_query($conn, $sql, $params);
$row=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
echo "<h1>Edit Profile</h1>";
echo "<form action'#' method='post' id='profile-add'>";
echo "<table cellpadding='5px' cellspacing='5px' class='tableform' >";
echo "<tr><td>SQL</td><td><textarea name='sqlprofile' id='sqlprofile' style='width:100%'>".htmlspecialchars($row['sql'],ENT_QUOTES)."</textarea></td></tr>";
echo "<tr><td style='width:150px'>Params</td><td><input type='text' name='paramsprofile' id='paramsprofile' autocomplete='off' value='".$row['params'] ."' /></td></tr>";
echo "<tr><td style='width:150px'>Name</td><td><input type='text' name='profilename' id='profilename' value='".htmlspecialchars($row['profilename'],ENT_QUOTES)."'/>";
echo "</td></tr><tr><td>";
echo "<input type='hidden' name='id' value='$id'/>";
echo "<input type='hidden' name='postback' value='update-profile'/>";
echo "<input type='submit' id='submit' value='Update' />";
echo "</td></tr></table>";
echo "</form>";I have tried encoding the serialised array with base64, using "magic_quotes", "htmlspecialchars" etc and nothing seems to work.
Can anyone advise how to correct or a better way to store the array for long term / cross session availability?
ASKER
Thanks. I'll give it a go.
ASKER
No luck.
I store the array in json in a session to pass it to another page, eg:
I then store the encoded array in a database:
The array is stored correctly in json format,
when I retrieve it I and decode it for the array, I get this, which works.:
When I then "edit" the database record, but make no changes,
I then get an array decoded as:
This then fails as it has the slashes before each apostrophe.
Any other ideas?
I store the array in json in a session to pass it to another page, eg:
$params=array($a, $b, $c);
$_SESSION['params'] = json_encode($params);I then store the encoded array in a database:
$params = $_SESSION['params'];
$sql="insert into tblprofiles params) values (?)";
$params=array($params);
$result=sqlsrv_query($conn, $sql, $params);The array is stored correctly in json format,
["smart","%smart%","test"]when I retrieve it I and decode it for the array, I get this, which works.:
string(59) "array ( 0 => 'smart', 1 => '%smart%', 2 => 'test', )"When I then "edit" the database record, but make no changes,
if(($_POST['postback']=='update-profile')){
$id=($_POST['id']);
$paramsprofile=$_POST['paramsprofile'];
$profilename=($_POST['profilename']);
$sql="update tblprofiles set profilename=?, sql=?, params=? where id=?";
$params=array($profilename, $sqlprofile, $paramsprofile, $id);
$result=sqlsrv_query($conn, $sql, $params);
}
$id=test($_GET['id']);
$sql="select params from tblprofiles where id=?";
$params=array($id);
$result=sqlsrv_query($conn, $sql, $params);
$row=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
echo "<h1>Edit Profile</h1>";
echo "<form action'#' method='post' id='profile-add'>";
echo "<input type='text' name='paramsprofile' id='paramsprofile' autocomplete='off' value='".$row['params'] ."' />";
echo "<input type='hidden' name='id' value='$id'/>";
echo "<input type='hidden' name='postback' value='update-profile'/>";
echo "<input type='submit' id='submit' value='Update' />";
echo "</form>";I then get an array decoded as:
string(67) "'array ( 0 => \'smart\', 1 => \'%smart%\', 2 => \'test\', )'"This then fails as it has the slashes before each apostrophe.
Any other ideas?
I am not clear on this bit
When I then "edit" the database record, but make no changes,How does that relate to the code you posted
ASKER
It shows the method I use to update the database record that holds the "params" array if I need to make any changes to it.
The array stays fine when I retrieve and decode it:
Until I use this form. Once I "update" the record, the array in the database still looks good, as below:
but when I retrieve it from the database and json_decode it, I am faced with an array that looks like
which does not work against my previously store sql query because something is adding the slashes
The array stays fine when I retrieve and decode it:
string(59) "array ( 0 => 'smart', 1 => '%smart%', 2 => 'test', )"Until I use this form. Once I "update" the record, the array in the database still looks good, as below:
["smart","%smart%","WROES"]but when I retrieve it from the database and json_decode it, I am faced with an array that looks like
string(67) "'array ( 0 => \'smart\', 1 => \'%smart%\', 2 => \'test\', )'"which does not work against my previously store sql query because something is adding the slashes
Not sure where and how you're encoding and decoding, but this look off to me:
---
The array stays fine when I retrieve and decode it:
string(59) "array ( 0 => 'smart', 1 => '%smart%', 2 => 'test', )"
---
That tells me that the data is a 59 character string, when in fact it should be a 3 element array. If you're storing that string in your DB, then when you do this:
echo "<input type='text' name='paramsprofile' id='paramsprofile' autocomplete='off' value='".$row['params'] ."' />";
You are probably just outputting that string into your INPUT field, and when you then submit your form, it will slashify the content.
---
The array stays fine when I retrieve and decode it:
string(59) "array ( 0 => 'smart', 1 => '%smart%', 2 => 'test', )"
---
That tells me that the data is a 59 character string, when in fact it should be a 3 element array. If you're storing that string in your DB, then when you do this:
echo "<input type='text' name='paramsprofile' id='paramsprofile' autocomplete='off' value='".$row['params'] ."' />";
You are probably just outputting that string into your INPUT field, and when you then submit your form, it will slashify the content.
So let me understand.
You put the array in the db. It looks fine and you can retrieve it and display it
You update the database with the form - still looks fine i.e. identical to the above
You retrieve it and now it has ' in ?
Slashes are added to database fields to escape ' that could be used in an injection attack. This is fairly common practice.
What does not make sense is that you say after an update the array looks identical to its previous state - but is decoded differently - that part does not make sense.
You put the array in the db. It looks fine and you can retrieve it and display it
You update the database with the form - still looks fine i.e. identical to the above
You retrieve it and now it has ' in ?
Slashes are added to database fields to escape ' that could be used in an injection attack. This is fairly common practice.
What does not make sense is that you say after an update the array looks identical to its previous state - but is decoded differently - that part does not make sense.
ASKER
I'll build a model and put it online to show you. Might take me a bit of time though.
ASKER
Ref your previous comment - exactly the point! it does not make sense!
Sounds like you might be manually manipulating your serialized data. For example:
That's not valid. That says it's an 181 character string, but it's only 155 characters. So unless you're manually redacting/editing data just for posting them on here, then that is a problem.
The extra slashes makes it sound like you're double-escaping somewhere. Since it happens on updates rather than creation, the logical place to look would be to see if you're escaping data when it comes out of the database, so that when you put it onto the form, you end up with extra slashes.
string(181) "array ( 0 => 'WROES', 1 => 'john', 2 => 'john%', 3 => 'baker', 4 => 'baker%', 5 => '', 6 => '%', 7 => '', 8 => '', 9 => '', 10 => '', 11 => '', 12 => '', )"That's not valid. That says it's an 181 character string, but it's only 155 characters. So unless you're manually redacting/editing data just for posting them on here, then that is a problem.
The extra slashes makes it sound like you're double-escaping somewhere. Since it happens on updates rather than creation, the logical place to look would be to see if you're escaping data when it comes out of the database, so that when you put it onto the form, you end up with extra slashes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Mohan, Good idea.
I'm not working on the project at the moment but will use it as a workaround when I get back to the job.
I'm not working on the project at the moment but will use it as a workaround when I get back to the job.
ASKER
Thanks Mohan, Good idea.
I'm not working on the project at the moment but will use it as a workaround when I get back to the job.
I'm not working on the project at the moment but will use it as a workaround when I get back to the job.
Comma separation is not a good idea in my experience. If your data contains commas you have a problem - now you have to start checking for quotes to ignore commas.
The standard practice is to use serialize() or preferably json_encode() - allows for structuring of the data - no issue with data contents as the encode / decode will escape key characters and no more work than an explode.
The standard practice is to use serialize() or preferably json_encode() - allows for structuring of the data - no issue with data contents as the encode / decode will escape key characters and no more work than an explode.
I agree with Julian - comma-separated is usually not the best way unless you're dealing 100% with raw numbers. Once string data enters the picture, you'll need to start checking for quotes and escaped quotes, and since that's the problem you currently have with serialized data, you'll be back to where you started.
You really need to solve the underlying issue you have with escaped quotes, since that will impact any other fields that have quotes. We didn't hear back from you after the last round of comments, so it's a little odd that you accepted a comment about CSV as the answer.
You really need to solve the underlying issue you have with escaped quotes, since that will impact any other fields that have quotes. We didn't hear back from you after the last round of comments, so it's a little odd that you accepted a comment about CSV as the answer.
json_encode() / json_decode()