Community discussions

MikroTik App
 
User avatar
amitkw
just joined
Topic Author
Posts: 13
Joined: Fri Jun 30, 2017 3:38 am
Location: Indonesia
Contact:

Execute RouterOS Command Like SQL

Fri Apr 23, 2021 4:39 am

i just wrote a class that allows executing RouterOS command like SQL command (select, insert, update, delete).

i wrote this class in the PHP programming language and is still dependend to the class written by Denis Basta (denis.basta@gmail.com).

the idea is using menu names instead of table names. The menu structure is separated by (-) character which is stored in the "*.ini" file.
for example ip-pool will access pool menu as table name or interface-wireless will access wireless menu as table name. More detail fork me on github (https://github.com/tauhidcp/PHPMikAPISQL)

CONNECT
<?php

require('../PHPMikAPISQL.php');

$config['host'] = "192.168.56.101";
$config['user'] = "admin";
$config['pass'] = "";
$config['port'] = "8728";
	
$MikSQL = new PHPMikAPISQL($config);

?>
SELECT
Retrieves data from the router. to print the router's interface list you can use "select * from interface" or print only specific item "select .id, name from interface". this command can be combined with the search clause where or/and sorting using "order by .id asc" or "order by .id desc". use limit keyword at the last to limit output "select * from interface limit 2". also, you can search with pattern using like keyword "select * from log where message like 'via api' order by .id desc limit 3"
<?php

# Example Select 

require('connect.php');

# Select all
$print = $MikSQL->ExecuteSQL("select .id,time,message from log where message like 'via api' order by .id desc limit 5");
#$print = $MikSQL->ExecuteSQL("select .id,name,type,mac-address from interface where name like 'eth'");
#$print = $MikSQL->ExecuteSQL("select .id,name,type,mac-address from interface where name like 'isp' and type='ether' order by .id desc limit 2");
#$print = $MikSQL->ExecuteSQL("select * from interface order by .id desc");

if ($print['status']!="FALSE"){
	
//print_r($print['data']);

// or
// print specific log item

foreach ($print['data'] as $row){
	
	echo $row['.id']."<br>";
	echo $row['time']."<br>";
	echo $row['message']."<br>";
	echo "-----------------------<br>";
	// dst...
	
} 
// or
// print specific interface item

/*foreach ($print['data'] as $row){
	
	echo $row['.id']."<br>";
	echo $row['name']."<br>";
	echo $row['mac-address']."<br>";
	echo "-----------------------<br>";
	// dst...
	
} */

} else {
	
	echo $print['message'];
	
} 

?>
INSERT
Add config item to the router. to add user to the router you can use "insert into user (name, group) values ​​('budi', 'full')". just change the table's field with menu's attribute.
<?php

# Example Insert 

require('connect.php');

# Insert New User
$user  = "budi";
$group = "full"; # full/read/write
$exec = $MikSQL->ExecuteSQL("insert into user (name,group) values ('".$user."','".$group."')");

# Insert New IP Address
#$address   = "192.168.88.1/24";
#$network   = "192.168.88.0"; 
#$interface = "ether4"; 
#$exec = $MikSQL->ExecuteSQL("insert into ip-address (address,network,interface) values ('".$address."','".$network."','".$interface."')");

if ($exec['status']=="TRUE"){
	
	echo $exec['message'];
	
} else {
	
	echo $exec['message']; 
	
}

?>
UPDATE
Update the router config item. to edit user from the router you can use "update user set group = 'read' where .id = '*2'". similar to insert command, the table's field is menu's attribute.
<?php

# Example Update

require('connect.php');

# Update User
$group = "read"; # full/read/write
$id    = "*4";
$exec  = $MikSQL->ExecuteSQL("update user set group='".$group."' where .id='".$id."'");

# Update IP Address
#$address   = "172.188.77.1/23";
#$network   = "172.188.77.0"; 
#$interface = "ether4"; 
#$id        = "*4";
#$exec = $MikSQL->ExecuteSQL("update ip-address set address='".$address."',network='".$network."',interface='".$interface."' where .id='".$id."'");

if ($exec['status']=="TRUE"){
	
	echo $exec['message'];
	
} else {
	
	echo $exec['message']; 
	
}

?>
DELETE
Remove config item from the router. to remove user from the router you can use "delete from user where .id = '*2'".
<?php

# Example Delete

require('connect.php');

# Delete User
$id   = "*4";
$exec = $MikSQL->ExecuteSQL("delete from user where .id='".$id."'");

#Delete IP Address
#$exec = $MikSQL->ExecuteSQL("delete from ip-address where .id='".$id."'");

if ($exec['status']=="TRUE"){
	
	echo $exec['message'];
	
} else {
	
	echo $exec['message']; 
	
}

?>

Who is online

Users browsing this forum: No registered users and 26 guests