Forums

What is the best way to escape database INSERTS?

Permalink 1 user found helpful
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!
stephendmalloy
 
Doki replied on at Permalink Reply
Doki
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.


Willing to take suggestions to improve this...
Vinzent replied on at Permalink Reply
Vinzent
This stuff is still vulnerable to XSS...
mkly replied on at Permalink Reply
mkly
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 at Permalink Reply
stephendmalloy
Thanks!
computronix replied on at 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 at Permalink Reply
Mainio
I believe he meant the text helper there:
$th = Loader::helper('text');
mkly replied on at Permalink Reply
mkly
Indeed it's a typo. @Manio is correct. Thanks @Manio.
mkly replied on at Permalink Best Answer Reply
mkly
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 at Permalink Reply
Vinzent
so I can insert a $_GET variable directly into a $db->Execute() ?
mkly replied on at Permalink Reply
mkly
-
http://stackoverflow.com/questions/76359/binding-variables-to-param...
http://phplens.com/lens/adodb/docs-adodb.htm...
Vinzent replied on at Permalink Reply
Vinzent
sweet ty :D
aryeh replied on at Permalink Reply
how would i do that same thing with an sql update?
stephendmalloy replied on at Permalink Reply
stephendmalloy
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 at Permalink Reply
Call to undefined method Concrete\Core\Database\Connection\Connection::AutoExecute()
stephendmalloy replied on at Permalink Reply
stephendmalloy
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 at Permalink Reply
5.7.2
ramonleenders replied on at Permalink Reply
ramonleenders
$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 at Permalink Reply
thank you!
i just found this post:
https://github.com/concrete5/concrete5-5.7.0/wiki/Migration-Guide#us...