Properly escaping SQL

[Alfred Vink]

Hi Guys,

Rookie question perhaps, but somehow I can’t get it to work.

How do I properly escape the $subject and $message values in this SQL query ?

tried using $db->quote() but apparently not using it properly.

//archive mail

$date = date("Y-m-j");
$author = $user->name;
$query = $db->getQuery(true);
$query = "INSERT INTO `#__gcmails` (`author`, `functie`, `type`, `date`,`subject`,`message`) VALUES ('$author','$func','$type','$date',$db->quote('$subject'),$db->quote('$message'))";

$db->setQuery( $query );
$db->execute();

Alfred

[Mike Smith]

$query = "INSERT INTO #__gcmails (author, functie, type, date, subject, message) VALUES ('$author','$func','$type','$date',$db->quote($ubject),$db->quote($message))";

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

[Mike Smith]

messed that up, sorry. Better still:
$query = "INSERT INTO #__gcmails (author, functie, type, date, subject, message) VALUES ('$author','$func','$type','$date','$db->quote($ubject)','$db->quote($message)')";

[Alfred Vink]

Tried that but doesn’t work, it doesn’t seem to be ‘escaped'

In case $subject = schema’s I get this error 

Met vriendelijke groeten,

Alfred Vink

Op 24 oktober 2015 bij 20:56:27, Mike Smith (b10...@gmail.com) schreef:

[Mike Smith]

Read my second post again, you don't need all those quotes ;)

[Alfred Vink]

Doesn’t change a thing…

It still trips on the first single quote in my $subject variable

Met vriendelijke groeten,

Alfred Vink

Op 24 oktober 2015 bij 21:43:46, Mike Smith (b10...@gmail.com) schreef:

[Mike Smith]

check the content of 

$db->quote($subject)

seems I missed the s from $subject

[Alfred Vink]

I noticed and it’s already in there...

Met vriendelijke groeten,

Alfred Vink

Op 24 oktober 2015 bij 22:03:34, Mike Smith (b10...@gmail.com) schreef:

[Chris Davenport]

It's generally better to use JDatabaseQuery to build your query strings, as described here:

https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase
https://docs.joomla.org/Selecting_data_using_JDatabase

Chris.

Chris Davenport
Joomla Production Leadership Team

[Hannes Papenberg]

$query = 'INSERT INTO `#__gcmails` (`author`, `functie`, `type`,

`date`,`subject`,`message`) VALUES (' . $db->quote($author) . ',' .
$db->quote($func) . ',' . $db->quote($type) . ',' . $db->quote($date) .
',' . $db->quote($subject) . ',' . $db->quote($message) . ')'; Never
expect PHP code in double-quotes to work. Your string evaluates to

$query = "INSERT INTO `#__gcmails` (`author`, `functie`, `type`,
`date`,`subject`,`message`) VALUES

('$author','$func','$type','$date',->quote('$subject'),->quote('$message'))";
since it tries to convert $db to a string, which results in an empty
string in the best case and a fatal error in the worst case. variables
in double quotes are replaced with their content, but function/method
calls are not executed. Always use single quotes.

Hannes

Am 24.10.2015 um 20:35 schrieb Alfred Vink:
> Hi Guys,
>
> Rookie question perhaps, but somehow I can’t get it to work.
> How do I properly escape the $subject and $message values in this SQL
> query ?
> tried using $db->quote() but apparently not using it properly.
>
> //archive mail

> $date = date("Y-m-j"); $author= $user->name; $query =

> $db->getQuery(true); $query = "INSERT INTO `#__gcmails` (`author`,
> `functie`, `type`, `date`,`subject`,`message`) VALUES
> ('$author','$func','$type','$date',$db->quote('$subject'),$db->quote('$message'))";
> $db->setQuery( $query ); $db->execute();
> Alfred

> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-gene...@googlegroups.com

> <mailto:joomla-dev-gene...@googlegroups.com>.

> To post to this group, send email to
> joomla-de...@googlegroups.com

> <mailto:joomla-de...@googlegroups.com>.

[Alfred Vink]

Hannes,

Thanks for your elaborate answer it worked great, but i guess you knew that ;-)

Met vriendelijke groeten,

Alfred Vink

Op 24 oktober 2015 bij 22:09:21, 'Hannes Papenberg' via Joomla! General Development (joomla-de...@googlegroups.com) schreef:

To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.

[cdcvineyard]

You must have the function/method calls outside of the double-quotes that are part of the query. Changes shown in yellow

$query = "INSERT INTO `#__gcmails` (`author`, `functie`, `type`, `date`,`subject`,`message`) VALUES ('$author','$func','$type','$date'," . $db->quote('$subject') . ", " . $db->quote('$message') . ")";

[ALFsoft]

Thanks, Hannes also pointed me in the same direction.

-- 
Alfred Vink

Op 27 oktober 2015 bij 17:37:17, cdcvineyard (chri...@gmail.com) schreef:

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.

To post to this group, send email to joomla-de...@googlegroups.com.

[cdcvineyard]

Another option would be to use the sprintf command to fill data into a formatted string. such as:

$query = sprintf("INSERT INTO `#__gcmails` (`author`, `functie`, `type`, `date`,`subject`,`message`) VALUES ('%s','%s','%s','%s', '%s', '%s')", $author, $func, $type, $date, $db->quote('$subject'), $db->quote('$message');

This is a little easier to read and determine where/what the variables are.

[Hannes Papenberg]

But that will still not properly quote all your input. If someone sends
in "');UPDATE #__users SET password = MD5('secret');" for $author, all
your passwords are suddenly set to "secret". Which is why everything
needs to be quoted.

In this special case the last 2 %s would not have to be put into single
quotes, since they are already quoted with $db->quote().

Hannes

Am 28.10.2015 um 16:22 schrieb cdcvineyard:
> Another option would be to use the sprintf command to fill data into a
> formatted string. such as:
>
> $query = sprintf("INSERT INTO `#__gcmails` (`author`, `functie`,
> `type`, `date`,`subject`,`message`) VALUES ('%s','%s','%s','%s', '%s',

> '%s')", $author, $func, $type, $date,$db->quote('$subject'),

> $db->quote('$message');
>
> This is a little easier to read and determine where/what the variables
> are.

> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-gene...@googlegroups.com

> <mailto:joomla-dev-gene...@googlegroups.com>.

> To post to this group, send email to
> joomla-de...@googlegroups.com

> <mailto:joomla-de...@googlegroups.com>.

[cdcvineyard]

The $db->quote() call handles the SQL injection attempts.  Each of the %s positions in the format is replaced by the sequential additional values passed to sprintf()  [functions return values].  So the first '%s' becomes'authors name' and the last becomes '\)\;Update XXXX_users Set password=md5(\'secret\');' 

Everything in the double-quotes is the format template, which includes quoting of the string values being substituted in.  I did notice that I should have stripped the single-quotes from the arguments to the $db->quote routines, and I missed a closing ')".

On Wednesday, October 28, 2015 at 10:26:15 AM UTC-5, Hannes Papenberg wrote:

But that will still not properly quote all your input. If someone sends
in "');UPDATE #__users SET password = MD5('secret');" for $author, all
your passwords are suddenly set to "secret". Which is why everything
needs to be quoted.

In this special case the last 2 %s would not have to be put into single
quotes, since they are already quoted with $db->quote().

Hannes

Am 28.10.2015 um 16:22 schrieb cdcvineyard:
> Another option would be to use the sprintf command to fill data into a
> formatted string. such as:
>
> $query = sprintf("INSERT INTO `#__gcmails` (`author`, `functie`,
> `type`, `date`,`subject`,`message`) VALUES ('%s','%s','%s','%s', '%s',
> '%s')", $author, $func, $type, $date,$db->quote('$subject'),
> $db->quote('$message');
>
> This is a little easier to read and determine where/what the variables
> are.
> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to joomla-dev-general+unsub...@googlegroups.com
> <mailto:joomla-dev-general+unsub...@googlegroups.com>.