Developing (v7+)

What is the best way to escape database INSERTS?

Permalink 1 user found helpful September 13, 2011 at 3:42 PM

I'm wondering what the best way to escape database inserts are. For example, with the following input:

!@#$%^&*()"'

I've tried:

$db = Loader::db();
// Need to look into escaping
//$grpName = $db->qstr($grpName);
//$grpName = htmlspecialchars($grpName);
$grpName = mysql_real_escape_string($grpName);
// Create group
$sql = "INSERT INTO goLabCollectionGroups VALUES('','$grpName','ENABLED','0');";

The only thing though that works for this is mysql_real_escape_string() which I've read should not be used. Can anyone help? The other two methods result in a mysql error.

Thanks!

Doki replied on Sep 13, 2011 at 6:47 pm Permalink Reply

I use:

function MakeSafe($unsafestring) 
   {
       if (get_magic_quotes_gpc())
       {
           $unsafestring = stripslashes($unsafestring);
       }
      $search = array ("'<script[^>]*?>.*?</script>'si", // Strip out javascript
      "'&(amp¦#38);'i",
      "'&(lt¦#60);'i",
      "'&(gt¦#62);'i",
      "'&(nbsp¦#160);'i",
      "'&(iexcl¦#161);'i",
      "'&(cent¦#162);'i",
      "'&(pound¦#163);'i",
      "'&(copy¦#169);'i",

Viewing 15 lines of 29 lines. View entire code block.

 
function MakeSafe($unsafestring) 
   {
       if (get_magic_quotes_gpc())
       {
           $unsafestring = stripslashes($unsafestring);
       }
 
      $search = array ("'<script[^>]*?>.*?</script>'si", // Strip out javascript
      "'&(amp¦#38);'i",
      "'&(lt¦#60);'i",
      "'&(gt¦#62);'i",
      "'&(nbsp¦#160);'i",
      "'&(iexcl¦#161);'i",
      "'&(cent¦#162);'i",
      "'&(pound¦#163);'i",
      "'&(copy¦#169);'i",
      "'&#(\d+);'e"); // evaluate as php
 
      $replace = array ("",
      "&",
      "<",
      ">",
      " ",
      chr(161),
      chr(162),
      chr(163),
      chr(169),
      "chr(\\1)");
 
      $unsafestring = preg_replace($search, $replace, $unsafestring);
 
       return mysql_escape_string($unsafestring);       
   }

Willing to take suggestions to improve this...

Vinzent replied on Jul 21, 2012 at 1:23 pm Permalink Reply

This stuff is still vulnerable to XSS...

mkly replied on Oct 13, 2011 at 8:58 pm Permalink Reply

EDIT: See below this code is braindead.

AFAIK its adodbhttp://adodb.sourceforge.net/

$th = Loader::helper('text');
$db = Loader::db();
$q = "INSERT INTO goLabCollectionGroup VALUES(?, ?, ?, ?);";
$v = array('', $grpName, 'ENABLED', '0');
$v = array_map(array($th, 'sanitize'), $v);
$res = $db->query($q, $v);

...is what I do.

stephendmalloy replied on Jan 21, 2012 at 10:46 pm Permalink Reply

Thanks!

computronix replied on Apr 17, 2012 at 2:02 pm Permalink Reply

What is the $th variable? I tried using this code block, but I got the following error:

Warning: array_map() expects parameter 1 to be a valid callback, first array member is not a valid class name or object

Mainio replied on Apr 17, 2012 at 3:08 pm Permalink Reply

I believe he meant the text helper there:

$th = Loader::helper('text');

mkly replied on Apr 17, 2012 at 3:12 pm Permalink Reply

Indeed it's a typo. @Manio is correct. Thanks @Manio.

mkly replied on Apr 17, 2012 at 3:15 pm Permalink Best Answer Reply

Although the above code is totally braindead on my part.

$db = Loader::db();
$db->Execute(
  'INSERT INTO goLabCollectionGroup VALUES(?, ?, ?, ?)',
  array(
    '',
    $grpName,
    'ENABLED',
    0
  )
);

adodb escapes for you. That above post was from when I was an idiot.

Vinzent replied on Jul 21, 2012 at 1:24 pm Permalink Reply

so I can insert a $_GET variable directly into a $db->Execute() ?

mkly replied on Jul 21, 2012 at 1:57 pm Permalink Reply

-
http://stackoverflow.com/questions/76359/binding-variables-to-param...
http://phplens.com/lens/adodb/docs-adodb.htm...

Vinzent replied on Jul 21, 2012 at 2:03 pm Permalink Reply

sweet ty :D

aryeh replied on Nov 25, 2014 at 7:22 pm Permalink Reply

how would i do that same thing with an sql update?

stephendmalloy replied on Nov 25, 2014 at 7:28 pm Permalink Reply

I've found the best way is to do something like:

$db = Loader::db();
$vals = array();
$vals['column_name_a'] = "value";
$vals['column_name_b'] = "value";
$vals['column_name_c'] = "value";
$recordID = 1;
$db->AutoExecute("tableName", $vals, "UPDATE", "id = $recordID");

aryeh replied on Nov 25, 2014 at 7:34 pm Permalink Reply

Call to undefined method Concrete\Core\Database\Connection\Connection::AutoExecute()

stephendmalloy replied on Nov 25, 2014 at 7:38 pm Permalink Reply

Looks like the method doesn't exist - what version of C5 are you running? I do this all the time however I have to note that this is right from memory and not tested.

Have a look here at the ADODB docs:

http://phplens.com/lens/adodb/docs-adodb.htm#autoexecute...

Hope this helps.

aryeh replied on Nov 25, 2014 at 7:44 pm Permalink Reply

5.7.2

ramonleenders replied on Nov 25, 2014 at 8:26 pm Permalink Reply

$db = Loader::db(); 
$id = (int)'THE_ID_YOU_WANT_TO_UPDATE';
$data = array(
'cID' => 3,
'fID'  => 5,
);
$db->update('yourTableName', $data, array('id' => $id));

All the keys in the $data array, are your table columns and the values behind it the ones to be inserted. Assuming you're using 5.7.2 as you stated above that is.

aryeh replied on Nov 25, 2014 at 8:30 pm Permalink Reply

thank you!
i just found this post:
https://github.com/concrete5/concrete5-5.7.0/wiki/Migration-Guide#us...

Forums

Developing (v7+)

What is the best way to escape database INSERTS?

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?