1. When we do a SQL query, if we want to pass value to query, we make use %d for integers or %s for strings. Similarly do we have any place holders for arrays. 2. I am facing a below typical problem, can somebody tell me what should I do? Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (*'PHP', 'HTML', 'SQL'*) But in above query search string *'PHP', 'HTML', 'SQL' *is hard coded. I want to make the query generic. Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query. Thanks Austin
This syntax is a hold over from the old C programming language and I have seen several of the newer Drupal books recommending it, not sure if there is a preference in D7. but essentially what nan wich (intrigued by the name) says is correct, if you build your query string it (as should all strings) begin with a single or double quote and end with the same. I personally can't see any problem coding multiple %s and %d placeholders in one string then coding them as a list of variables, but don't forget that your string and list need to be passed to a function (fscanf comes to mind), but I believe the new drupal get db functions (sorry I don't remember their names) do this. Warren Vail On 4/10/2011 5:55 PM, Austin Einter wrote:
1. When we do a SQL query, if we want to pass value to query, we make use %d for integers or %s for strings. Similarly do we have any place holders for arrays. 2. I am facing a below typical problem, can somebody tell me what should I do? Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN (*'PHP', 'HTML', 'SQL'*) But in above query search string *'PHP', 'HTML', 'SQL' *is hard coded. I want to make the query generic. Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query. Thanks Austin
On Sun, Apr 10, 2011 at 6:55 PM, Austin Einter <austin.einter@gmail.com> wrote:
2. I am facing a below typical problem, can somebody tell me what should I do?
Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ('PHP', 'HTML', 'SQL')
But in above query search string 'PHP', 'HTML', 'SQL' is hard coded. I want to make the query generic.
Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query.
There's a handy function you need called db_placeholders - http://api.drupal.org/api/drupal/includes--database.inc/function/db_placehol... There are some examples on that page, but if you want more help be sure to provide more of your example code. Cheers, Greg -- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
Not sure what you mean by generic, and never used the db_placeholders function, but this should work; $skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $query = sprintf("SELECT skillid FROM %s WHERE skillname in ('%s')", $tblname, "'".implode("', '",$skillnames)."'"); //execute and fetch query results here not real elegant, but nothing is as elegant as the solution that works and maintainers can understand. Warren Vail On 4/10/2011 6:33 PM, Greg Knaddison wrote:
On Sun, Apr 10, 2011 at 6:55 PM, Austin Einter<austin.einter@gmail.com> wrote:
2. I am facing a below typical problem, can somebody tell me what should I do?
Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ('PHP', 'HTML', 'SQL')
But in above query search string 'PHP', 'HTML', 'SQL' is hard coded. I want to make the query generic.
Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query.
There's a handy function you need called db_placeholders -
http://api.drupal.org/api/drupal/includes--database.inc/function/db_placehol...
There are some examples on that page, but if you want more help be sure to provide more of your example code.
Cheers, Greg
Le dimanche 10 avril 2011 à 22:05 -0700, Warren Vail a écrit :
Not sure what you mean by generic, and never used the db_placeholders function, but this should work;
$skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $query = sprintf("SELECT skillid FROM %s WHERE skillname in ('%s')", $tblname, "'".implode("', '",$skillnames)."'"); //execute and fetch query results here
not real elegant, but nothing is as elegant as the solution that works and maintainers can understand.
You can use the db_placeholders() function. $skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $placeholder = db_placeholders($skillnames, 'varchar'); $query = "SELECT skillid FROM {%s} WHERE skillname in (" . $placeholder . ")"; db_query($query, array($tblname) + $skillnames); Is another alternative, maybe a bit more "drupalistic". You can compact it to: $skillnames = array("PHP", "HTML","SQL"); db_query("SELECT skillid FROM {%s} WHERE skillname in (" . db_placeholders($skillnames, 'varchar') . ")"; array("resubmt_skills") + $skillnames); Pierre.
Thanks Warren, Pierre I got it working with place hoolders. Best Regards Austin On Mon, Apr 11, 2011 at 10:35 AM, Warren Vail <warren@vailtech.net> wrote:
Not sure what you mean by generic, and never used the db_placeholders function, but this should work;
$skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $query = sprintf("SELECT skillid FROM %s WHERE skillname in ('%s')", $tblname, "'".implode("', '",$skillnames)."'"); //execute and fetch query results here
not real elegant, but nothing is as elegant as the solution that works and maintainers can understand.
Warren Vail
On 4/10/2011 6:33 PM, Greg Knaddison wrote:
On Sun, Apr 10, 2011 at 6:55 PM, Austin Einter<austin.einter@gmail.com> wrote:
2. I am facing a below typical problem, can somebody tell me what should I do?
Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ('PHP', 'HTML', 'SQL')
But in above query search string 'PHP', 'HTML', 'SQL' is hard coded. I want to make the query generic.
Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query.
There's a handy function you need called db_placeholders -
http://api.drupal.org/api/drupal/includes--database.inc/function/db_placehol...
There are some examples on that page, but if you want more help be sure to provide more of your example code.
Cheers, Greg
-- [ Drupal support list | http://lists.drupal.org/ ]
On Sun, Apr 10, 2011 at 11:05 PM, Warren Vail <warren@vailtech.net> wrote:
Not sure what you mean by generic, and never used the db_placeholders function, but this should work;
$skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $query = sprintf("SELECT skillid FROM %s WHERE skillname in ('%s')", $tblname, "'".implode("', '",$skillnames)."'"); //execute and fetch query results here
not real elegant, but nothing is as elegant as the solution that works and maintainers can understand.
This may work, but is _wrong_. Please read http://heine.familiedeelstra.com/a-security-vulnerability-waiting-to-happen Thanks, Greg -- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
Your article seems to suggest that the whole concept of db_placeholders is not valid. How would you do any query where parameters come from a form without this vulnerability? Warren Vail -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Greg Knaddison Sent: Monday, April 11, 2011 8:50 AM To: support@drupal.org Cc: Warren Vail Subject: Re: [support] Place holders in SQL query On Sun, Apr 10, 2011 at 11:05 PM, Warren Vail <warren@vailtech.net> wrote:
Not sure what you mean by generic, and never used the db_placeholders function, but this should work;
$skillnames = array("PHP", "HTML","SQL"); $tblname = "resubmt_skills"; $query = sprintf("SELECT skillid FROM %s WHERE skillname in ('%s')", $tblname, "'".implode("', '",$skillnames)."'"); //execute and fetch query results here
not real elegant, but nothing is as elegant as the solution that works and maintainers can understand.
This may work, but is _wrong_. Please read http://heine.familiedeelstra.com/a-security-vulnerability-waiting-to-happen Thanks, Greg -- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials -- [ Drupal support list | http://lists.drupal.org/ ]
On Mon, Apr 11, 2011 at 10:21 AM, Warren Vail <warren@vailtech.net> wrote:
Your article seems to suggest that the whole concept of db_placeholders is not valid. How would you do any query where parameters come from a form without this vulnerability?
Please re-read the article and the comment on the article. Thanks, Greg -- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
OK, Clearer understanding on the article and the function of db_placeholders, thanks for the patience all. Surprised a bit I didn't wind up on fire. Warren Vail -----Original Message----- From: Greg Knaddison [mailto:greg@growingventuresolutions.com] Sent: Monday, April 11, 2011 9:34 AM To: support@drupal.org Cc: Warren Vail Subject: Re: [support] Place holders in SQL query On Mon, Apr 11, 2011 at 10:21 AM, Warren Vail <warren@vailtech.net> wrote:
Your article seems to suggest that the whole concept of db_placeholders is not valid. How would you do any query where parameters come from a form without this vulnerability?
Please re-read the article and the comment on the article. Thanks, Greg -- Greg Knaddison | 720-310-5623 | http://growingventuresolutions.com http://masteringdrupal.com - Videos and Tutorials
<:avglsdata id=avglsdata> This is exacty what db_placeholders() is for. It takes an array and build the correct number of %s fields (or %d depending on the type parameter) for inserting into your IN clause. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. ________________________________ From: Austin Einter <austin.einter@gmail.com> To: support@drupal.org Sent: Sun, April 10, 2011 8:55:16 PM Subject: [support] Place holders in SQL query 1. When we do a SQL query, if we want to pass value to query, we make use %d for integers or %s for strings. Similarly do we have any place holders for arrays. 2. I am facing a below typical problem, can somebody tell me what should I do? Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ('PHP', 'HTML', 'SQL') But in above query search string 'PHP', 'HTML', 'SQL' is hard coded. I want to make the query generic. Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query. Thanks Austin
I'm beginning to see that the root of his problem was that one of his parameters was an array and there is no place holder for inserting an array (might be a nice enhancement, don't you think? "%as" or "%s[]" for an array of strings, carried in the array of parameters as a nested array. The other alternative was to recognize this and do the implode to turn the nested array into a string external to db_placeholders. Warren Vail _____ From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of nan wich Sent: Monday, April 11, 2011 5:31 AM To: support@drupal.org Subject: Re: [support] Place holders in SQL query This is exacty what db_placeholders() is for. It takes an array and build the correct number of %s fields (or %d depending on the type parameter) for inserting into your IN clause. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. _____ From: Austin Einter <austin.einter@gmail.com> To: support@drupal.org Sent: Sun, April 10, 2011 8:55:16 PM Subject: [support] Place holders in SQL query 1. When we do a SQL query, if we want to pass value to query, we make use %d for integers or %s for strings. Similarly do we have any place holders for arrays. 2. I am facing a below typical problem, can somebody tell me what should I do? Below query works fine. SELECT skillid FROM {resubmt_skills} WHERE resubmt_skills.skillname IN ('PHP', 'HTML', 'SQL') But in above query search string 'PHP', 'HTML', 'SQL' is hard coded. I want to make the query generic. Lets say in custom search form, user entered search string PHP, Telecom, HTML,, so how can I write a generic query. Thanks Austin
participants (5)
-
Austin Einter -
Greg Knaddison -
nan wich -
Pierre Rineau -
Warren Vail