What went wrong led to prevent use LIMIT function on MySQL query using php?

Question

i have this query

public function getGameHomes_limit($page,$limit){
    $query = sprintf('SELECT %1$sserver_homes.*,%1$sremote_servers.*, %1$sconfig_homes.*
        FROM `%1$sserver_homes` NATURAL JOIN `%1$sconfig_homes` NATURAL JOIN `%1$sremote_servers`; ',
        $this->table_prefix);
    return $this->listQuery($query);
}

and i need to set LIMIT

public function getGameHomes_limit($page,$limit){
    $query = sprintf('SELECT %1$sserver_homes.*,%1$sremote_servers.*, %1$sconfig_homes.*
        FROM `%1$sserver_homes` LIMIT '.$page.','.$limit.' NATURAL JOIN `%1$sconfig_homes` NATURAL JOIN `%1$sremote_servers`; ',
        $this->table_prefix);
    return $this->listQuery($query);
}

but i have this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NATURAL JOIN ogp_config_homes NATURAL JOIN ogp_remote_servers' at line 2

http://bobby-tables.com/ Don't build SQL queries by string concatenation or variable substitution. Your code is wide open to SQL injection attacks. — GordonM, Mar 14 '17 at 21:24
please can you explain in example how can i protect my code from injection ? — Obada Diab, Mar 14 '17 at 21:28
Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Mar 14 '17 at 21:33

score 2 · Accepted Answer · answered Mar 14 '17 at 21:16

2

Change the order of your query and make LIMIT the last bit like so:

public function getGameHomes_limit($page,$limit){
    $query = sprintf('SELECT %1$sserver_homes.*,%1$sremote_servers.*, %1$sconfig_homes.*
        FROM `%1$sserver_homes` NATURAL JOIN `%1$sconfig_homes` NATURAL JOIN `%1$sremote_servers` LIMIT '.$page.','.$limit.'; ',
        $this->table_prefix);
    return $this->listQuery($query);
}

answered Mar 14 '17 at 21:16

Morgs

1,558
3
19
34

Great, consider marking it as an answer so the rest don't spend time giving more answers. – Morgs Mar 14 '17 at 21:27

What went wrong led to prevent use LIMIT function on MySQL query using php?

1 Answers1