How to insert serialized array in MySQL DB using mysqli_real_escape_string()

Question

I have tried endless variations of mysqli_real_escape_string() to insert a simple array in a column ni one of my tables. The contents of the column is blank everytime.

Can anyone see anything wrong with this query?

$accessRights = array(
  'S-01' => 'Y',
  'S-02' => 'Y',
);
$accessRights = serialize( $accessRights );

mysqli_query( $GLOBALS['db_link'], 'SET NAMES "utf8"' ); // to ensure proper encoding of special characters
$query_string = '
  UPDATE users_accessRights
  SET
    accessRights = "' . mysqli_real_escape_string( $accessRights ) . '"
  WHERE userID = "' . $_POST['userID'] . '"
  LIMIT 1
';
mysqli_query( $GLOBALS['db_link'], $query_string ) or die( mysqli_error( $GLOBALS['db_link'] ) );

Note: I can use this query to insert a simple text string into the table, so I know the query itself works.

Why not save yourself all this trouble and use a prepared statement with bound parameters? Also, you should use mysqli_set_charset() instead of the SET NAMES query — Phil
– Phil, Commented Nov 2, 2015 at 0:03
Terrific @Phil - I like saving myself trouble... Now the question is what does your suggestion mean and how do I go about doing that? — H. Ferrence
– H. Ferrence, Commented Nov 2, 2015 at 0:07

Phil · Accepted Answer · 2015-11-02 00:23:43Z

2

It seems to me that you do not have sufficient error reporting enabled to have noticed a syntax error in your code. mysqli_real_escape_string() requires two arguments, not one. You should make sure your development environment's php.ini has the following entries set

error_reporting = E_ALL
display_errors = On

Alternatively, place this at the top of your script but remove it for production

ini_set('display_errors', 'On');
error_reporting(E_ALL);

A better way to create queries with variable / user input is prepared statements. For example

// set mysqli to throw exceptions so you don't have to check return
// values for false or use "or die"
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = $GLOBALS['db_link']; // I suggest you avoid using globals like this ASAP

$mysqli->set_charset('utf8');

$stmt = $mysqli->prepare(
    'UPDATE users_accessRights SET accessRights = ? WHERE userID = ? LIMIT 1');
$stmt->bind_param('si', $accessRights, $_POST['userID']);
$stmt->execute();

This assumes that userID is an integer field.

edited Nov 2, 2015 at 0:23

answered Nov 2, 2015 at 0:10

Phil

166k25 gold badges265 silver badges269 bronze badges

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

3 Comments

H. Ferrence Over a year ago

Nice, I'll give it a try -- thanks @Phil...(and yes, userID is an integer -- key field)

Phil Over a year ago

@H.Ferrence cool, that's what the "i" in "si" is for (the "s" being for "string"). Let me know how you go and make sure you read that prepared statement quickstart guide for a deeper understanding.

H. Ferrence Over a year ago

Thanks @Phil. I got it to work via mysqli_real_escape_string( $GLOBALS['db_link'], $accessRights ). I need to look into Prepared Statements and begun to understand that method. Thanks for the help and the tip.

Collectives™ on Stack Overflow

How to insert serialized array in MySQL DB using mysqli_real_escape_string()

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related