How to run this mysql query

Question

I am trying to load all records from the database that have been created by the current logged on user in Joomla, im not sure if its possible to have php inside a mysql query though ?, if not how would I go about doing this otherwise ?

SELECT
  leadname,
  businessname,
  postcode,
  gasoiluser,
  dervuser,
  kerouser,
  cf_uid,
  cf_id
FROM
  #__chronoforms_data_addupdatelead
WHERE createdby = '<?php
  $user =& JFactory::getUser(); echo $user->get('name') ; ?>'
ORDER BY cf_created DESC

uh? what prevents you from using a variable and placing it inside the query? Is this inside Joomla, right? — Damien Pirsy, Dec 22 '11 at 13:00
when I run this I get 'no data found', even though there are records that are created by the logged in user — Iain Simpson, Dec 22 '11 at 13:02
please provide more context. do you use this query inside a php file? how do you run it? — codeling, Dec 22 '11 at 13:12

score 2 · Accepted Answer · edited Dec 23 '11 at 05:34

2

Of course you can access PHP variables for creating the query - as the #__ prefix suggests, you're already running your query from "inside Joomla". Which means it is in php, and something like this should do what you want:

$user =& JFactory::getUser();
$db =& JFactory::getDBO();
if (!$user->guest) {
    $query = 'SELECT leadname, businessname, postcode, gasoiluser, '.
        ' dervuser, kerouser, cf_uid, cf_id '.
        ' FROM #__chronoforms_data_addupdatelead '.
        ' WHERE createdby = '.$db->Quote($user->name)).
        ' ORDER BY cf_created DESC';
    $db->setQuery($query);
}

But a little more context would help us see what you'll have to do exactly - what's the code around the SQL query - is it in a php file?

Remember, echo prints to the Response, which is not what you want to do in this case, you want to change the query; so just concatenate the variable content to your query, as shown above; and you should actually be already be in php mode where this query is defined, so the <?php tag is of no use (but again, too few context to be sure about this)!

edited Dec 23 '11 at 05:34

Johan

74,508
24
191
319

answered Dec 22 '11 at 13:07

codeling

11,056
4
42
71

-1 for putting an SQL-injectable `get` in an [php] + [sql] answer. At 1k+ rep you should know better. – Johan Dec 22 '11 at 13:38
As @Johan correctly pointed out, setQuery() doesn't escape the parameters, but justs returns the query string (substituting/adding the #__ when needed). Although, I must say, the $user->get('name') shouldn't be a problem, unless the user really is the infamous little Bobby :)..Joking aside, that value should already be safe since already treated somewhere else, but I'm not 100% sure about that. IIRC, Joomla has the $db->escape($value) or something similar – Damien Pirsy Dec 22 '11 at 13:49
+1, Your answer is absolutely ok, just one note: in Joomla, the proper way to 'escape' variables is using $db->Quote( $xxx ) for values and $db->nameQuote( 'xxx' ) for names (table names, field names..). – alghimo Dec 22 '11 at 14:36
@alghimo, your comment is incorrect (I could say clueless, but that would be rude). Quote does not escape anything, it merely puts `'` quotes around it, table, column and field names cannot be escaped, they need to be whitelisted. If you don't Bobby Tables will get you. – Johan Dec 22 '11 at 18:52
Hi Johan. $db->Quote automatically calls $db->getEscaped (in Joomla 1.5) or $db->escape (in Joomla 1.7, I don't know which one gets called in Joomla 1.6), unless you set the second parameter to false. Besides, getEscaped has deprecated in Joomla 1.7. Many thanks for your comment, it has make me check again something I had for sure and that's always good :) I've also seen that $db->escape has a second parameter to add "extra" escaping (in the case of MySQL db, it calls addcslashes after calling mysql_real_escape_string. If you want to use that, then it's better to use $db->escape – alghimo Dec 22 '11 at 22:48
@alghimo, I'm beginning to like Joomla, it does seem to get a lot of things right. Note that field/column etc names still need to be whitelisted. They cannot be escaped by any means, see: http://stackoverflow.com/questions/5811834/how-to-prevent-sql-injection-with-dynamic-tablenames – Johan Dec 23 '11 at 05:34

score -1 · Answer 2 · answered Dec 22 '11 at 13:14

mysql is a database server that accepts a string and returns either result set or an error

string passed to mysql must be proper SQL statement

what you have in your question is not proper SQL statement, it is a string waiting to be parsed by PHP and whoever knows by who else

this part is PHP for sure:

<?php $user =& JFactory::getUser(); echo $user->get('name') ; ?>

this part is something like a placeholder for correct table name that is replaced at runtime of whatever you got this query from(supposedly joomla):

#__chronoforms_data_addupdatelead

if you want to run that query you must to figure out what to substitute with the aforementioned blocks

question was if it's possible to have php inside mysql query and i guess i answered that — keymone, Dec 22 '11 at 18:04

How to run this mysql query

2 Answers2