Mod Authors: SMF 2.0 Database Functions

[daRaL]

thanks

[OutofOrder]

I've noticed that you can't reference one same value twice (or more) inside a query.
As of SMF 2 RC1-2, this is not possible:

Code Select Expand


<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist})
OR column_b IN ({array_int:numlist})',
array( 'numlist' => $myArray ));
?>
for such code will throw an error "The database value you're trying to insert does not exist: numlist".
I think this is because of the way preg_replace_callback works: in smf_db_quote(), the call to smf_db_replacement__calback() function won't return a match for the second time {array_int:numlist} is parsed.

If I wanted to get that query working, the fix would have to be this one:

Code () Select Expand


<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist1})
AND column_b IN ({array_int:numlist2})',
array( 'numlist1' => $myArray, 'numlist2' => $myArray ));
?>
And I'd be passing the same array twice. If you worry about efficiency, this could get you worried a tiny bit.

[vbgamer45]

I would consider that a bug

[JPDeni]

Would anybody mind if I backed up a little bit and asked for some explanations of the explanation? For example:

$smcFunc['db_query'] (identifier, query, values, connection)

    * Works Similar to how db_query worked in 1.x versions.
    * Identifier is used for identifying specific queries that will be handled specially.
    * Values is an array of values you are intending to use in the query.

Code Select Expand


            $result = $smcFunc['db_query']('', '
                SELECT poster_time
                FROM {db_prefix}messages
                WHERE id_msg = {int:id_msg}
                LIMIT 1',
                array(
                    'id_msg' => $user_settings['id_msg_last_visit'],
                )
            );


We're told that there is an identifier which is "used for identifying specific queries that will be handled specially." How and when would such an identifier be used? What specific queries would use one? How would they be handled?

Then there's

Code Select Expand


{int:id_msg}


which seems to relate to

Code Select Expand


'id_msg' => $user_settings['id_msg_last_visit'],


except that the int part has not been defined or explained. What does it mean? Is it always there? Are there other things that might be used instead of int?

What happens when there are multiple criteria in the "WHERE" clause? Where does an "AND" or "OR" go? Can I get a simple example?

Or....

Is there some place I can go to get this information? If I need help with php, I usually go to php.net. Is there something similar to learn whatever language this is written in?

[SoLoGHoST]

I've noticed that you can't reference one same value twice (or more) inside a query.
As of SMF 2 RC1-2, this is not possible:

Code Select Expand


<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist})
OR column_b IN ({array_int:numlist})',
array( 'numlist' => $myArray ));
?>
for such code will throw an error "The database value you're trying to insert does not exist: numlist".
I think this is because of the way preg_replace_callback works: in smf_db_quote(), the call to smf_db_replacement__calback() function won't return a match for the second time {array_int:numlist} is parsed.

If I wanted to get that query working, the fix would have to be this one:

Code () Select Expand


<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist1})
AND column_b IN ({array_int:numlist2})',
array( 'numlist1' => $myArray, 'numlist2' => $myArray ));
?>
And I'd be passing the same array twice. If you worry about efficiency, this could get you worried a tiny bit.

First off, your query above implies 2 different methods.  Are you trying to get the row when both columns equal the integer found in the array (implied in your 2nd query by using AND) OR are you trying to get a row of all columns when either column_a OR column_b equal any of the values in the integer array (implied in your 1st query using OR).

Ok, I have no clue why you would need to define numlist1 and numlist2 to both equal the same array.  All you need is 'numlist' => $myArray

Here's how you would do it  (Returns all column info from mytable when either column_a OR column_b equals any integer values found within $myArray):

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT *
FROM mytable
WHERE (column_a IN ({array_int:numlist}) OR column_b IN ({array_int:numlist}))',
array(
'numlist' => $myArray,
)
);
Have tested this and works perfect.  I think your problem is that you didn't encapsulate the entire condition within the paranthesis, which should be done.  Unless I am misunderstanding what you are saying here.

Cheers

[SoLoGHoST]

Would anybody mind if I backed up a little bit and asked for some explanations of the explanation? For example:

$smcFunc['db_query'] (identifier, query, values, connection)

    * Works Similar to how db_query worked in 1.x versions.
    * Identifier is used for identifying specific queries that will be handled specially.
    * Values is an array of values you are intending to use in the query.

Code Select Expand


            $result = $smcFunc['db_query']('', '
                SELECT poster_time
                FROM {db_prefix}messages
                WHERE id_msg = {int:id_msg}
                LIMIT 1',
                array(
                    'id_msg' => $user_settings['id_msg_last_visit'],
                )
            );


We're told that there is an identifier which is "used for identifying specific queries that will be handled specially." How and when would such an identifier be used? What specific queries would use one? How would they be handled?

Then there's

Code Select Expand


{int:id_msg}


which seems to relate to

Code Select Expand


'id_msg' => $user_settings['id_msg_last_visit'],


except that the int part has not been defined or explained. What does it mean? Is it always there? Are there other things that might be used instead of int?

What happens when there are multiple criteria in the "WHERE" clause? Where does an "AND" or "OR" go? Can I get a simple example?

Or....

Is there some place I can go to get this information? If I need help with php, I usually go to php.net. Is there something similar to learn whatever language this is written in?

I'm not entirely sure on all of the different types of identifiers there are.  I haven't found a section here at SMF that explains this at all, perhaps there is one somewhere here on SMF.  Not entirely sure on where to go to actually find all of the different types of identifiers that can be used either.  So I'm with you on this 1.  Could use a bit more of explanation on this.

Answering your 2nd question....

int is just a way for the function to identify the the expected type.  There are currently 4 types that I know about that can be used here:

int, string, array_int, and array_string.  I could be wrong, but I believe this to be all of them that can be used.  Since everything is either a string or an integer in some way or another.  This is one of the extra security features in SMF 2.0.  Since this doesn't link it directly to the database query without giving it a specific type to link it too and can not be used by member for input so as to gain Admin Rights on your forums, thus the array() of values.  Didn't quite understand this at first either, but now, I couldn't imagine coding without it.

For Example:

WHERE column_a = {int:myValue',}
array( 'myValue' => $blah, )

As you can see the bold letters here, so you can see a pattern hopefully.  Basically, you define the array index by the type and then give it a value.  If you do not give it a value, it will not exist and cause an error.

Old way of doing this was:

WHERE column_a = $blah

Problem with the old way of doing it, if $blah did not make it an integer this could be passed as a string and cause problems since the only values in column_a would be of integer type.
ARANTOR - BEAT ME TO IT!!  How Dare you...lol, j/k

[Fustrate]

well, it's also SQL

These are all of the valid types for {______:varname}: (found in Subs-db-mysql.php, smf_db_replacement__callback())
int
string
text
array_int
array_string
date
float
identifier
raw

it'll generally escape them correctly, and throw an error if you try to send the wrong data type (e.g. "My String" is not a float). 'raw' won't do anything to it, just plop it in place and hope you've escaped it all correctly. 'identifier'... I have no clue about.

[Fustrate]

There's also {db_prefix}, {query_see_board}, and  {query_wanna_see_board}

[JPDeni]

Thanks for all the info. I'll see what I can digest.

What would happen if I said

Code Select Expand


WHERE id_msg = 3


IOW, what if I didn't do that whole array thing and int thing, but just put in a value like is in a MySQL query? Would I get an error?

There's also {db_prefix}, {query_see_board}, and  {query_wanna_see_board}

One confusing thing at a time! 

[Fustrate]

it will work, but it's best to use the {type:var} way because SMF will sanitize the data for you, and hopefully prevent any stray SQL injections.

[Arantor]

Thanks for all the info. I'll see what I can digest.

What would happen if I said

Code Select Expand


WHERE id_msg = 3


IOW, what if I didn't do that whole array thing and int thing, but just put in a value like is in a MySQL query? Would I get an error?

If it will always be 3 and NEVER change based on user input you can do that - and it'll work. Just don't expect to see it approved if you use it in a mod though.

If there's any element of that value coming from the user, you REALLY should use this method of parameterising the query for protection against injections.

[SoLoGHoST]

Yes, and raw...knew that 1, lol.  Just didn't click.  Thanks for the float and date Frustrate.  What would you use indentifier for??  For Example {identifier:myID} and than how would you link it for array( 'myID' => ?, ).  Just curious.

Thanks...

[JPDeni]

Just don't expect to see it approved if you use it in a mod though.

I'm just trying to get the things that I write for myself to work!

[Fustrate]

Solo, as I said, I have no idea what identifier is I think it has something to do with `database_name`.`table_name`, since it talks about backticks not being needed, which would mean database_name.table_name, which is valid for MySQL.

[SoLoGHoST]

Okie Dokie.  Thanks

[SoLoGHoST]

well, it's also SQL

These are all of the valid types for {______:varname}: (found in Subs-db-mysql.php, smf_db_replacement__callback())
...
...
text
...
...

it'll generally escape them correctly, and throw an error if you try to send the wrong data type (e.g. "My String" is not a float). 'raw' won't do anything to it, just plop it in place and hope you've escaped it all correctly. 'identifier'... I have no clue about.

Didn't realize you had this in there.  {text:varname} doesn't work as a valid type when querying the database.  gotta use {string:varname}

[Fustrate]

That's a bug, then. If you look at the function I mentioned, both cases (text and string) result in the same code being executed.

[SoLoGHoST]

I know RC1-1 is still not the final release, and I know this is something that must have been considered already, given the quality that the SMF devs always look for this piece of software, so please take this question as a humble reminder
Will it be allowed for column creation with the UNSIGNED attribute? Currently RC1-1 can't handle it and it's not possible for a mod to be designed with this in mind. Also, if a table has to be created having columns that replicate columns from default SMF tables (i.e. id_member), the created columns won't be true replicas from the originals.

Wow, just now seeing this, good to know.  So while I create MODS that have integers with an unsigned attribute, I suppose will have to use $smcFunc['db_query'] to accomplish this than.  Sad that it doesn't do this, was under the impression that it would do this by saying...

'unsigned' => true,

within the array when building it to pass to the $smcFunc['db_add_column'] function.

[Arantor]

I don't believe it does, no, but should be fixed for RC2 if it hasn't been already (at least that was my understanding)

[SoLoGHoST]

Currently updating my MoDs using $smcFunc['db_query'] instead of the new database functions.  Only for unsigned integers.  Whenever SMF RC2 Final is released I suppose I will change it back.  But for now, I think unsigned integers in my mods are important so this needs to be addressed.

Cheers