Cookie Consent by Free Privacy Policy Generator ๐Ÿ“Œ CodeSOD: High Performance Query

๐Ÿ  Team IT Security News

TSecurity.de ist eine Online-Plattform, die sich auf die Bereitstellung von Informationen,alle 15 Minuten neuste Nachrichten, Bildungsressourcen und Dienstleistungen rund um das Thema IT-Sicherheit spezialisiert hat.
Ob es sich um aktuelle Nachrichten, Fachartikel, Blogbeitrรคge, Webinare, Tutorials, oder Tipps & Tricks handelt, TSecurity.de bietet seinen Nutzern einen umfassenden รœberblick รผber die wichtigsten Aspekte der IT-Sicherheit in einer sich stรคndig verรคndernden digitalen Welt.

16.12.2023 - TIP: Wer den Cookie Consent Banner akzeptiert, kann z.B. von Englisch nach Deutsch รผbersetzen, erst Englisch auswรคhlen dann wieder Deutsch!

Google Android Playstore Download Button fรผr Team IT Security



๐Ÿ“š CodeSOD: High Performance Query


๐Ÿ’ก Newskategorie: Programmierung
๐Ÿ”— Quelle: thedailywtf.com

Aaron was doing some work with a high-performance-computing platform. The kind of thing that handles complex numerical simulations divided across farms of CPUs, GPUs, FPGAs, and basically anything else that computes.

The system comes with a web GUI, and the code for the web GUI isโ€ฆ wellโ€ฆ let's just look at the comment on a method.

/**
    * This method executes a select query.
    * @param filter associative array containing the table field as key and a value.
    * @param table
    * @param groupby array containing group by fields.
    * @param orderby field to sort
    * @param order ( desc or asc )
    * @return A double array containing the query result.
    */

Oh yeah, we're looking at PHP which generates SQL queries on the fly. And you know they happen via string concatenation. Even with that knowledge, however, it's actually probably worse than you think.

public function select( $filter, $table, $groupby, $orderby, $order='desc', $limit=0 )
    {
        $filter_str = "";
        $groupby_str = "";
        $field_str = "";
        $is_first = TRUE;
        $join_annex = 0;
        $i = 0;
        if ( $table == MAIN_TABLE )
                $annex_schema = $this->getSchema( ANNEX_TABLE );
        else
                $annex_schema = null;

        if( $filter )
        {
            foreach( $filter as $field => $value )
            {
                if (($annex_schema != null) && (in_array($field, array_keys($annex_schema))))
                        # need to join on annex table
                        $join_annex = 1;

                if( $value != "")
                {
                           $filter_str .= ($is_first ? '' : ' AND ');

                        if( preg_match( '`^.*(\*|\?).*$`', $value ) ) {
                                $compar = ' LIKE ';
                                $value = strtr( $value, array( '?' => '.', '*' => '%' ));
                        } else {
                                $compar = '=';
                        }

                        if ( $field == PATH ) {
                                $match_array = db_path_match($value);
                                if ( array_count_values( $match_array ) == 1 )
                                        $filter_str .= $field.$compar.'\''.$value.'\'';
                                else {
                                        $filter_str .= '(';
                                        $is_first_subexpr = TRUE;
                                        foreach ($match_array as $expr ) {
                                                if (preg_match( '`^.*(\%|\_).*$`', $expr ))
                                                        $filter_str .= ($is_first_subexpr ? '': ' OR ').$field.' LIKE \''.$expr.'\'';
                                                else
                                                        $filter_str .= ($is_first_subexpr ? '': ' OR ').$field.'=\''.$expr.'\'';

                                                $is_first_subexpr = FALSE;
                                        }
                                        $filter_str .= ')';
                                }
                         } else {
                                $filter_str .= $field.$compar.'\''.$value.'\'';
                        }
                    $is_first = FALSE;
                }
            }
        }

        if( $groupby )
        {
            $is_first = TRUE;
            foreach( $groupby as $field )
            {
                if (($annex_schema != null) && (in_array($field, array_keys($annex_schema))))
                        # need to join on annex table
                        $join_annex = 1;

                $groupby_str = $groupby_str.($is_first ? '' : ',').$field;
                $is_first = FALSE;
            }
        }

        /* /!\ is there a field in ANNEX_INFO? */

        $is_first = TRUE;
        foreach( $this->getSchema( $table ) as $field => $type )
        {
            if( substr_count( $type, "int" ) != 0 && $groupby && $field != "status" ) //TODO status case (no meaning here)
                $field_str .= ( $is_first ? '' : ', ' )." SUM(".($join_annex ? "$table." : "").$field.")";
            else
                $field_str .= ( $is_first ? '' : ', ' ).($join_annex ? "$table." : "").$field;
            $is_first = FALSE;
        }
        if ($join_annex) {
                foreach( $this->getSchema( ANNEX_TABLE ) as $field => $type )
                {
                    if( substr_count( $type, "int" ) != 0 && $groupby && $field != "status" ) //TODO status case (no meaning here)
                        $field_str .= ( $is_first ? '' : ', ' )." SUM(".($join_annex ? ANNEX_TABLE."." : "").$field.")";
                    else
                        $field_str .= ( $is_first ? '' : ', ' ).($join_annex ? ANNEX_TABLE."." : "").$field;
                    $is_first = FALSE;
                }
        }

        try
        {
            if ($join_annex)
                    $query = "SELECT ".$field_str." FROM ".$table." LEFT JOIN ".ANNEX_TABLE." ON $table.id = ".ANNEX_TABLE.".id ".
                                ( $filter ? " WHERE ".$filter_str : "" ).
                                ( $groupby ? " GROUP BY ".$groupby_str : "" ).( $orderby ? " ORDER BY ".$orderby." ".$order : "" ).
                                ( $limit > 0 ? " LIMIT $limit" : "" );
            else
                    $query = "SELECT ".$field_str." FROM ".$table.( $filter ? " WHERE ".$filter_str : "" ).
                                ( $groupby ? " GROUP BY ".$groupby_str : "" ).( $orderby ? " ORDER BY ".$orderby." ".$order : "" ).
                                ( $limit > 0 ? " LIMIT $limit" : "" );

            $result = $this->connection->query( $query );

            $i=0;
            while( $line = $result->fetch( PDO::FETCH_ASSOC ) )
            {
                $returned_result[$i] = $line;
                $i++;
                if ($i > MAX_SEARCH_RESULT)
                {
                    echo "<b>ERROR: max result count exceeded</b><br>\n";
                    return null;
                }
            }
            $result->closeCursor();
            $this->rowNumber = $i;

            return $returned_result;
        }
        catch( PDOException $e )
        {
            echo 'Error: '.$e->getMessage().'</br>';
        }
    }

There's just so much in here. We start by detecting if we need to join to an ANNEX_TABLE based on whether or not our query target is the MAIN_TABLE. We loop over the filter, possibly joining to that table. We munge the comparison field with a regex, to see if we're doing an = comparison or a LIKE comparison.

The whole thing is a tortured pile of string munging and SQL injection and so much complexity to handle all of the weird little cases they've included.

The code is bad, through and through. But I think my favorite bits are the error handling: we just print an error to the page, complete with hard-coded </br> tags, which, umโ€ฆ the break tag is not meant to be an open/close tag pair. Even if we're doing older XHMTL style, the correct way to output that would be <br />.

Special credit to this line: echo "<b>ERROR: max result count exceeded</b><br>\n";, and that poor, useless \n hanging out at the end, too.

[Advertisement] Continuously monitor your servers for configuration changes, and report when there's configuration drift. Get started with Otter today!
...



๐Ÿ“Œ CodeSOD: High Performance Query


๐Ÿ“ˆ 41.9 Punkte

๐Ÿ“Œ CodeSOD: Regular Query String


๐Ÿ“ˆ 27.93 Punkte

๐Ÿ“Œ CodeSOD: An Operating Query


๐Ÿ“ˆ 27.93 Punkte

๐Ÿ“Œ CodeSOD: Set Your Performance Target


๐Ÿ“ˆ 23.79 Punkte

๐Ÿ“Œ Trend Micro ScanMail for Exchange 12.0 Log Query/Quarantine Query cross site scripting


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ Nelson Open Source ERP 6.3.1 db/utils/query/data.xml query sql injection


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ GitHub Security Lab: ihsinme: CPP add query for: CPP Add query for CWE-20 Improper Input Validation


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ CVE-2016-15020 | liftkit database up to 2.13.1 src/Query/Query.php processOrderBy sql injection


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ TANStack Query: How It Changes the Way You Query APIs


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ More DNS over HTTPS: Become One With the Packet. Be the Query. See the Query, (Thu, Dec 19th)


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ ISC BIND up to 9.9.4 DNS Query bin/named/query.c query_findclosestnsec3 NSEC3-Signed Zones memory corruption


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ Trend Micro ScanMail for Exchange 12.0 Log Query/Quarantine Query Cross Site Scripting


๐Ÿ“ˆ 23.34 Punkte

๐Ÿ“Œ Netgear Nighthawk RAXE500 Tri-Band Wi-Fi 6E Router, hands on: High-performance, high-capacity


๐Ÿ“ˆ 20.4 Punkte

๐Ÿ“Œ Julia 1.8.3 - A high-level, open source and high-performance dynamic language.


๐Ÿ“ˆ 20.4 Punkte

๐Ÿ“Œ Unlocking High-Performance to Achieve a High Ubuntu Server Administration


๐Ÿ“ˆ 20.4 Punkte

๐Ÿ“Œ Improve Query Performance by Managing Statistics in Azure SQL | Data Exposed: MVP Edition


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Accelerating query performance and availability with no code changes


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ SQL Server 2022 updates for query performance and database failover


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Understand Data Warehouse: Query Performance


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ SQL Query Performance Tuning in MySQL - Part 2


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Boost Your Query Performance with Function-Based Indexes in PostgreSQL


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Boosting Spark Union Operator Performance: Optimization Tips for Improved Query Speed


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Optimize cost and performance with Query Acceleration for Azure Data Lake Storage


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ Improve Query Performance by Managing Statistics in Azure SQL | Data Exposed


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ AWS Partition Projections: Enhancing Athena Query Performance


๐Ÿ“ˆ 19.2 Punkte

๐Ÿ“Œ CodeSOD: SQL with no Equal


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: The Parameters


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: Common Variables


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: Very Productive Code


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: The Email Process


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: An Array of Colors


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: Exhaustive Scheduling Options


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: In House Refactoring


๐Ÿ“ˆ 16.26 Punkte

๐Ÿ“Œ CodeSOD: Switching File Types


๐Ÿ“ˆ 16.26 Punkte











matomo