inserting strings into a database

Worker Joined: Feb 18, 2004 Posts: 244

When you have a variable string that might contain special charactes like ' or " or / or - how do you parse it before hand so that it will not generate a mysql error?

For instance pretent someone used a " while filling in the $author field here:

Code:

   $author = check_html($author, "nohtml");


    $db->sql_query("update ".$prefix."_rq set quote='$quote', author='$author' where qid='$qid'") or die(""._RQCRITICALERROR."");

It will cause a mysql error. So besides the check_html function is there a preexisting php function or nuke function that I can call to parse it further?

Thanks,
Darrell

registered · Site Admin Joined: Jun 04, 2004 Posts: 6437

Only registered users can see links on this board! Get registered or login! - but note the warnings on magic_quotes...

Posted: Mon Jan 01, 2007 7:55 pm

Ugh,

I got some more reading to do. Thanks for pointing me in the right direction.

kguske wrote:

Only registered users can see links on this board! Get registered or login! - but note the warnings on magic_quotes...

Posted: Mon Jan 01, 2007 8:02 pm

See also: Only registered users can see links on this board! Get registered or login!

Posted: Mon Jan 01, 2007 8:47 pm

Well, If I may, Im going to try my best to explain it.
Quoting variables really isnt necessary. But making sure the variables are set correctly is important. I hope you do a check well before putting it into this statement:

$author = check_html($author, "nohtml");
$db->sql_query('update `'.$prefix.'_rq set` quote='.$quote.', author='.$author.' where qid='.$qid.'') or die(''._RQCRITICALERROR.'');

using ' instead of " saves on time with the way php looks for variables. the ` around the table name just helps with syntax.

For the most part, variables are passed as quoted but do NOT contain quotes(If that makes any sense). if it is necessary to quote your inputs, this would be the best way:

$author = check_html($author, "nohtml");
$db->sql_query('update `'.$prefix.'_rq set` quote=\''.$quote.'\', author=\''.$author.'\' where qid=\''.$qid.'\'') or die(''._RQCRITICALERROR.'');

As it looks like Im " quoting them, Im in fact \' ' without the space.

Heres something to help you to understand it.

$var='this is plain text'.php is looking for a variable/constant here.'plain text again.';

Notice the periods? It should remind you of echo. How you can go
echo 'text'
.'more text'
.'final';

addslashes is not always your friend, niether is stripslashes. I hope this helps you to better grasp the understanding you are looking for.

Posted: Mon Jan 01, 2007 9:12 pm

He is talking about strings that already have a ' or something in them that will trip up the SQL engine.

registered · Posted: Mon Jan 01, 2007 10:39 pm

By the way, magic_quotes will automatically add the slashes upon any value that is coming in as INPUT to your scripts, such as: GET, POST, COOKIE, ...

So, it really depends very much on knowing exactly what you are dealing with within that string. Personally, I think it is good practice to always "cleanse" your input variables and then just prior to committing to the DB, use either of the two functions already mentioned (although, they are not foolproof in terms of ensuring they are "hack-proof", but that is why cleansing your input is always #1 priority prior to using the values within those variables).

A good book is "PHP Pro Security". I got mine copy from Amazon. Excellent read.

Posted: Tue Jan 02, 2007 4:35 pm

So is this good enough?

Code:

.


.


.


$quote = check_html($quote,"nohtml");


$quote = esc_quotes($quote);


$author = check_html($author, "nohtml");


$author = esc_quotes($author);


 


$db->sql_query("update ".$prefix."_rq set quote='$quote', author='$author' where qid='$qid'") or die(""._RQCRITICALERROR."");


.


.


.


function esc_quotes($astring)


{


   if (!get_magic_quotes_gpc()) {


       $astring = addslashes($astring);


   } 


   return $astring;


}

Thanks for all your input everyone.

Darrell

Posted: Tue Jan 02, 2007 10:45 pm

Well, alot depends on what you have done with these variables ahead of this code. If you have untouched them coming in from your INPUT (as described above), then I would feel uncomfortable with this. Again, it really depends on your usage.

What you might consider doing is right up front in the script doing your get_magic_quotes_gpc() check and do a "stripslashes" if its turned on first... however, I think it may depend on your patch level as to what check_html is doing (i.e., if its doing a stripslashes before it checks for HTML tags - but that can also cause issues... its a mess). I would check if your check_html function in mainfile.php is stripping the slashes...