first of all do not use INSERT into statement with $_GET variables,
it's an SQL injection.
here is an example of proper escaping:
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City");
$city = "'s Hertogenbosch";
/* this query will fail, cause we didn't escape $city */
if (!$mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
printf("Error: %s\n", $mysqli->sqlstate);
}
$city = $mysqli->real_escape_string($city);
/* this query with escaped $city will work */
if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}
$mysqli->close();
?>
adapt this to your example.
Secondly you might want to use sqlbox and put in between smsbox and
bearerbox so you'll have 2 tables: send_sms and sent_sms one for queue
and 2nd for archived messages.
2013/9/20 alex Preda <[email protected]>:
> I'm trying dlr-url with url : dlr-url = "http://x.x.x.x/rsms.php?sender=%p&;
> text=%a and for store dlr entry im using rsms.php. is it good way to store
> dlr entry in mysql or i changes required. here is my rsms.php file that are
> stored in /var/www/html.
>
> <?php
> $con = mysql_connect("localhost","root","redhat");
> if (!$con)
> {
> die('Could not connect: ' . mysql_error());
> }
> else{
> mysql_select_db("kannel");
> }
> $smsc = $_GET['smsc'];
> $ts = $_GET['ts'];
> $destination = $_GET['destination'];
> $source = $_GET['source'];
> $service = $_GET['service'];
> $url = $_GET['url'];
> $mask = $_GET['mask'];
> $status = $_GET['status'];
> $boxc = $_GET['boxc'];
> $message_id = $_GET['message_id'];
> $isisms = $_GET['isisms'];
>
> $sql="insert into dlr
> (smsc,boxc_id,ts,source,destination,service,url,mask,status,timestamp,changed)
> values
>
> ('$smsc','$boxc_id','$ts','$source','$destination','$service','$url','$mask','$status','$timestamp','$changed')";
> # echo mysql_query($sql);
>
> $query ="UPDATE dlr SET status='$status' WHERE message_id='$message_id'";
> echo $result = mysql_query($query) or die("Query failed : " .
> mysql_error());
>
> ?>
>
>
> On Fri, Sep 20, 2013 at 5:22 PM, arun sharma <[email protected]> wrote:
>>
>> I'm trying dlr-url with url : dlr-url =
>> "http://x.x.x.x/rsms.php?sender=%p&; text=%a and for store dlr entry im using
>> rsms.php. is it good way to store dlr entry in mysql or i changes required.
>> here is my rsms.php file that are stored in /var/www/html.
>>
>> <?php
>> $con = mysql_connect("localhost","root","redhat");
>> if (!$con)
>> {
>> die('Could not connect: ' . mysql_error());
>> }
>> else{
>> mysql_select_db("kannel");
>> }
>> $smsc = $_GET['smsc'];
>> $ts = $_GET['ts'];
>> $destination = $_GET['destination'];
>> $source = $_GET['source'];
>> $service = $_GET['service'];
>> $url = $_GET['url'];
>> $mask = $_GET['mask'];
>> $status = $_GET['status'];
>> $boxc = $_GET['boxc'];
>> $message_id = $_GET['message_id'];
>> $isisms = $_GET['isisms'];
>>
>> $sql="insert into dlr
>>
>> (smsc,boxc_id,ts,source,destination,service,url,mask,status,timestamp,changed)
>> values
>>
>> ('$smsc','$boxc_id','$ts','$source','$destination','$service','$url','$mask','$status','$timestamp','$changed')";
>> # echo mysql_query($sql);
>>
>> $query ="UPDATE dlr SET status='$status' WHERE message_id='$message_id'";
>> echo $result = mysql_query($query) or die("Query failed : " .
>> mysql_error());
>>
>> ?>
>>
>>
>>
>> On Fri, Sep 20, 2013 at 5:12 PM, Alejandro Guerrieri
>> <[email protected]> wrote:
>>>
>>> The dlr table only holds dlrs while in transit, they won't get stored
>>> long term. You need to create something using your dlr-url to records that.
>>>
>>> --
>>> Alex Guerrieri
>>>
>>> On Sep 20, 2013, at 7:26, alex Preda <[email protected]> wrote:
>>>
>>> Hello Kannel Team,
>>> Thanks Alejandro and Alvaro for your
>>> response.
>>>
>>> i had configured mysql database and create table with name of "dlr", my
>>> kannel service works fine and sms are send successfully. but didn't get dlr
>>> reports and also access.log didn't generate receive dlr fields. i made some
>>> changes in kannel.conf, here is my kannel.conf file,dlr table, and
>>> access.log. i'm bit confused with rsms.php. your help appreciated.
>>>
>>> Kannel.conf
>>>
>>> #CORE
>>> group = core
>>> admin-port = 13000
>>> smsbox-port = 13001
>>> admin-password = test
>>> log-file = "/var/log/kannel/kannel.log"
>>> log-level = 1
>>> box-deny-ip = "*.*.*.*"
>>> box-allow-ip = "127.0.0.1;x.x.x.x"
>>> admin-deny-ip = ""
>>> admin-allow-ip = ""
>>> unified-prefix = "00358,0"
>>> access-log = "/var/log/kannel/access.log"
>>> dlr-storage = mysql
>>>
>>> #SMSC
>>> group = smsc
>>> smsc = smpp
>>> smsc-id = "sms1"
>>> host = x.x.x.x
>>> smsc-username = test
>>> smsc-password = test
>>> port = 1234
>>> system-type = sms
>>>
>>> #smsbox
>>> group = smsbox
>>> bearerbox-host = localhost
>>> sendsms-port = 13013
>>> global-sender = 13013
>>> sendsms-chars = "0123456789 +-"
>>> log-file = "/var/log/kannel/smsbox.log"
>>> log-level = 0
>>> access-log = "/var/log/kannel/access.log"
>>>
>>> # sendsms-user
>>> group = sendsms-user
>>> username = test
>>> password = test
>>> user-deny-ip = ""
>>> user-allow-ip = ""
>>> default-sender = update
>>> dlr-url = "http://174.142.163.63/rsms.php?sender=%p&; text=%a"
>>> #dlr-mask = 31
>>>
>>> #smsroute
>>> group = smsbox-route
>>> smsbox-id = mysmsc
>>> smsc-id = "sms1"
>>> shortcode = "1111;2222"
>>>
>>> #sms-service
>>> group = sms-service
>>> keyword = default
>>> get-url = "http://x.x.x.x/rsms.php?sender=%p&; text=%a"
>>> accept-x-kannel-headers = true
>>> max-messages = 3
>>> concatenation = true
>>> catch-all = true
>>>
>>> # Example MYSQL Connection
>>> group = mysql-connection
>>> id = mydlr
>>> host = localhost
>>> username = root
>>> password = redhat
>>> database = kannel
>>> port = 3306
>>>
>>> #dlr-db
>>> group = dlr-db
>>> id = mydlr
>>> table = dlr
>>> field-smsc = smsc
>>> field-timestamp = ts
>>> field-destination = destination
>>> field-source = source
>>> field-service = service
>>> field-url = url
>>> field-mask = mask
>>> field-status = status
>>> field-boxc-id = boxc
>>>
>>> -----------------------------------------------------------------------------------------------------------------
>>> DLR Table
>>>
>>> mysql> desc dlr;
>>> +-------------+--------------+------+-----+---------+-------+
>>> | Field | Type | Null | Key | Default | Extra |
>>> +-------------+--------------+------+-----+---------+-------+
>>> | smsc | varchar(40) | YES | | NULL | |
>>> | ts | varchar(40) | YES | | NULL | |
>>> | destination | varchar(40) | YES | | NULL | |
>>> | source | varchar(40) | YES | | NULL | |
>>> | service | varchar(40) | YES | | NULL | |
>>> | url | varchar(255) | YES | | NULL | |
>>> | mask | int(10) | YES | | NULL | |
>>> | status | int(10) | YES | | NULL | |
>>> | boxc | varchar(40) | YES | | NULL | |
>>> +-------------+--------------+------+-----+---------+-------+
>>> 9 rows in set (0.00 sec)
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------------------
>>> Access.log
>>>
>>> [root@test kannel]# tail -f access.log
>>> 2013-09-20 14:48:05 Sent SMS [SMSC:sms1] [SVC:test] [ACT:] [BINF:] [FID:]
>>> [META:] [from:update] [to:9782000021] [flags:-1:0:-1:-1:-1] [msg:2:we]
>>> [udh:0:]
>>> 2013-09-20 14:50:46 Log ends
>>> 2013-09-20 14:50:55 Log begins
>>> 2013-09-20 14:51:00 Log begins
>>> 2013-09-20 14:51:13 send-SMS request added - sender:test:update
>>> 115.248.40.89 target:9782000021 request: 'we'
>>> 2013-09-20 14:51:13 Sent SMS [SMSC:sms1] [SVC:test] [ACT:] [BINF:] [FID:]
>>> [META:] [from:update] [to:9782000021] [flags:-1:0:-1:-1:-1] [msg:2:we]
>>> [udh:0:]
>>> 2013-09-20 14:51:17 send-SMS request added - sender:test:update
>>> 115.248.40.89 target:9782000021 request: 'we'
>>> 2013-09-20 14:51:17 Sent SMS [SMSC:sms1] [SVC:test] [ACT:] [BINF:] [FID:]
>>> [META:] [from:update] [to:9782000021] [flags:-1:0:-1:-1:-1] [msg:2:we]
>>> [udh:0:]
>>> 2013-09-20 14:53:01 send-SMS request added - sender:test:update
>>> 115.248.40.89 target:9782000021 request: 'we'
>>> 2013-09-20 14:53:02 Sent SMS [SMSC:sms1] [SVC:test] [ACT:] [BINF:] [FID:]
>>> [META:] [from:update] [to:9782000021] [flags:-1:0:-1:-1:-1] [msg:2:we]
>>> [udh:0:]
>>>
>>>
>>>
>>>
>>> On Thu, Sep 19, 2013 at 8:20 PM, Alejandro Guerrieri
>>> <[email protected]> wrote:
>>>>
>>>> MYSQL: Unknown database 'dlr'
>>>>
>>>> Did you create the database?
>>>>
>>>>
>>>> On Thu, Sep 19, 2013 at 10:26 AM, alex Preda <[email protected]>
>>>> wrote:
>>>>>
>>>>> Hello Alvaro,
>>>>> Thanks for your reply ! :)
>>>>> As per your instructions i tried to start bearerbox but it is showing
>>>>> some following error.
>>>>>
>>>>> [root@test1 kannel]# !b
>>>>> bearerbox -v 0 /etc/kannel.conf
>>>>> 2013-09-19 18:05:40 [30340] [0] INFO: Debug_lvl = 0, log_file = <none>,
>>>>> log_lvl = 0
>>>>> 2013-09-19 18:05:40 [30340] [0] ERROR: MYSQL: can not connect to
>>>>> database!
>>>>> 2013-09-19 18:05:40 [30340] [0] ERROR: MYSQL: Unknown database 'dlr'
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: DLR: MySQL: database pool has no
>>>>> connections!
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: bearerbox(gw_panic+0x147)
>>>>> [0x486067]
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: bearerbox(dlr_init_mysql+0x48e)
>>>>> [0x41c29e]
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: bearerbox(dlr_init+0x7c)
>>>>> [0x41a83c]
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: bearerbox(main+0xd9) [0x40e659]
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC:
>>>>> /lib64/libc.so.6(__libc_start_main+0xfd) [0x7eff9db35cdd]
>>>>> 2013-09-19 18:05:40 [30340] [0] PANIC: bearerbox() [0x40d6a9]
>>>>>
>>>>> Please check my kannel.conf file, if you find any misconfiguration
>>>>> please let me know.
>>>>>
>>>>> #CORE
>>>>> group = core
>>>>> admin-port = 13000
>>>>> smsbox-port = 13001
>>>>> admin-password = test
>>>>> log-file = "/var/log/kannel/kannel.log"
>>>>> log-level = 0
>>>>> box-deny-ip = "*.*.*.*"
>>>>> box-allow-ip = "127.0.0.1;x.x.x.x"
>>>>> admin-deny-ip = ""
>>>>> admin-allow-ip = ""
>>>>> unified-prefix = "00358,0"
>>>>> access-log = "/var/log/kannel/access.log"
>>>>> dlr-storage = mysql
>>>>>
>>>>> #SMSC
>>>>> group = smsc
>>>>> smsc = smpp
>>>>> smsc-id = "sms1"
>>>>> host = x.x.x.x
>>>>> smsc-username = test
>>>>> smsc-password = test
>>>>> port = 1234
>>>>> system-type = sms
>>>>>
>>>>> #smsbox
>>>>> group = smsbox
>>>>> bearerbox-host = localhost
>>>>> sendsms-port = 13013
>>>>> global-sender = 13013
>>>>> sendsms-chars = "0123456789 +-"
>>>>> log-file = "/var/log/kannel/smsbox.log"
>>>>> log-level = 0
>>>>> access-log = "/var/log/kannel/access.log"
>>>>>
>>>>> # sendsms-user
>>>>> group = sendsms-user
>>>>> username = test
>>>>> password = test
>>>>> user-deny-ip = ""
>>>>> user-allow-ip = ""
>>>>> default-sender = 123456
>>>>>
>>>>> #smsroute
>>>>> group = smsbox-route
>>>>> smsbox-id = mysmsc
>>>>> smsc-id = "sms1"
>>>>> shortcode = "1111;2222"
>>>>>
>>>>> #sms-service
>>>>> group = sms-service
>>>>> keyword = default
>>>>> accept-x-kannel-headers = true
>>>>> max-messages = 3
>>>>> concatenation = true
>>>>> catch-all = true
>>>>>
>>>>> # Example MYSQL Connection
>>>>> group = mysql-connection
>>>>> id = mydlr
>>>>> host = localhost
>>>>> username = root
>>>>> password = redhat
>>>>> database = dlr
>>>>> port = 3306
>>>>>
>>>>> #dlr-db
>>>>> group = dlr-db
>>>>> id = mydlr
>>>>> table = dlr
>>>>> field-smsc = smsc
>>>>> field-timestamp = ts
>>>>> field-destination = destination
>>>>> field-source = source
>>>>> field-service = service
>>>>> field-url = url
>>>>> field-mask = mask
>>>>> field-status = status
>>>>> field-boxc-id = boxc
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>> On Thu, Sep 19, 2013 at 7:23 PM, Alvaro Cornejo
>>>>> <[email protected]> wrote:
>>>>>>
>>>>>> Hi
>>>>>>
>>>>>> You need to first start bearerbox and have it listen on port 13001 as
>>>>>> you specified in its config.
>>>>>>
>>>>>> Regards
>>>>>>
>>>>>> Alvaro
>>>>>>
>>>>>> On 9/19/13, alex Preda <[email protected]> wrote:
>>>>>> > Hello kannel Team,
>>>>>> > I have installed gateway 1.5.0(v) with option
>>>>>> > --with-mysql, and installed kannel rpm kannel-1.4.3, I have been
>>>>>> > trying to set up *dlr-storage using mysql*. but im getting error in
>>>>>> > restart kannel. service failed to restart. but its work with
>>>>>> > dlr-storage = internal. i was check old posts in kannel user
>>>>>> > directory
>>>>>> > and find some related issues but still facing error. Thanks
>>>>>> >
>>>>>> >
>>>>>> > here is my log file of smsbox.
>>>>>> >
>>>>>> > 2013-09-19 14:50:54 [30003] [0] ERROR: connect failed 2013-09-19
>>>>>> > 14:50:54
>>>>>> > [30003] [0] ERROR: System error 111: Connection refused 2013-09-19
>>>>>> > 14:50:54
>>>>>> > [30003] [0] ERROR: error connecting to server `localhost' at port
>>>>>> > `13001'
>>>>>> > 2013-09-19 14:50:54 [30003] [0] PANIC: Couldn't connect to the
>>>>>> > bearerbox.
>>>>>> > 2013-09-19 14:50:54 [30003] [0] PANIC:
>>>>>> > /usr/sbin/smsbox(gw_panic+0x15a)
>>>>>> > [0x43ddfa] 2013-09-19 14:50:54 [30003] [0] PANIC:
>>>>>> > /usr/sbin/smsbox(main+0xca0) [0x40e530] 2013-09-19 14:50:54 [30003]
>>>>>> > [0]
>>>>>> > PANIC: /lib64/libc.so.6(__libc_start_main+0xfd) [0x7f2d47967cdd]
>>>>>> > 2013-09-19
>>>>>> > 14:50:54 [30003] [0] PANIC: /usr/sbin/smsbox() [0x40d1b9] 2013-09-19
>>>>>> > 14:50:58 [30040] [0] INFO: Added logfile
>>>>>> > `/var/log/kannel/smsbox.log' with
>>>>>> > level `0'.
>>>>>> >
>>>>>> > Regards
>>>>>> > Alex Preda
>>>>>> >
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> |-----------------------------------------------------------------------------------------------------------------|
>>>>>> Envíe y Reciba Datos y mensajes de Texto (SMS) hacia y desde cualquier
>>>>>> celular y Nextel
>>>>>> en el Perú, México y en mas de 180 paises. Use aplicaciones 2 vias via
>>>>>> SMS y GPRS online
>>>>>> Visitenos en www.perusms.NET www.smsglobal.com.mx y
>>>>>> www.pravcom.com
>>>>>
>>>>>
>>>>
>>>
>>
>>
>>
>> --
>> Arun Sharma
>>
>
