SMS Server Tools 3
This site is hosted by Kekekasvi.com
 Menu
Basic information:
Additional information:
Support:
Get SMS Server Tools 3:
Additional Options

 Sponsored links

 Search
Custom Search

 Visitor locations
 
 SMS Server Tools 3 Community
Welcome, Guest. Please login or register. Fri Mar 29, 2024 12:33
SMSTools3 Community » Help and support Bottom

[solved] Help with Checkhandler to verify number in SQL Database

  This topic is locked

Page:  1

Author Post
Member
Registered:
Jan 2010
Location: Bristol, United Kingdom
Operating system name and version: Windows XP
Version of smsd: 3.0
Smsd installed from: sources
Name and model of a modem / phone: Huwei E169
Interface: USB

I have started to use and event handler to send out information based on a code that is sent by the users to the server. This works fine and so I want to limit the outgoing SMS messages to users with mobile numbers that are stored in an SQL database. I looked in the SMS Tools handbook and there is a Checkhandler example for checking users details from an SQL database so I modified it to meet my needs.

I just want to check that the senders number is in the database and if it is then the response message is allowed to go out.

This is my Checkhandler.sh file:

#!/bin/sh

#Extract data from the SMS file
to=`formail -zx To: < $1`

#log into mysql
SQL_ARGS="-h host100 -u user99 -p xxxxxx -D sms -s -N -B -e"

#Check result against phone number column in database
result=`mysql $SQL_ARGS select number from users where number = \"$to\"

if [ -z "$result" ]; then
exit 1
fi

The database is called SMS the table is called USERS and the searched column is NUMBER, all of the numbers are in the format 44778512345

A typical response message after the event handler has processed the senders request looks like this:
To: 447785123456

1st Bill Muirhead 07785135790
2nd John Moulson 0778524680

The problem is that the checkhandler filters everything and so no messages are allowed out and the log file says that the checkhandler has disallowed the message. I guess I am missing something simple here but I can't figure it out. :roll:

I would be grateful for some advice.

Thanks

Bill.

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
bill1798 wrote
#Check result against phone number column in database
result=`mysql $SQL_ARGS select number from users where number = \"$to\"

if [ -z "$result" ]; then
exit 1
fi

Return value 1 from the checkhandler means that the message is rejected. Please check the syntax in the checkhandler script, there is missing ` at the end of a line:
result=`mysql $SQL_ARGS select number from users where number = \"$to\"`

Member
Registered:
Jan 2010
Location: Bristol, United Kingdom
Topic owner
Hello Keke

Thanks for your reply, I feel embarrassed at making such a basic error and I noticed that I had also missed a ; from the end of the line. I have checked all of the script and it looks good to me but now it does the opposite of before and lets every SMS through regardless of the number.

To try and test this script I have made a test script which is the same as the checkhandler.sh but I have removed the if statement from the end and replaced it with echo "$result". When I run this from the Cygwin command line I get the following error

$ test.sh
c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe Ver 14.12 Distrib 5.0.45,
Usage: c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe [OPTIONS] [databas
C:\my.ini C:\my.cnf C:\WINDOWS\my.ini C:\WINDOWS\my.cnf c:\Program Files\MySQL\
show-warnings FALSE00847216E)efault value)---------------------re read

Does this mean that I have an error in the mysql call?

The checkhandler script is simply an edited version of the script on page 227 of Stephan Fring's manual so I think that it should be more or less correct yet it never seems to return the correct exit code.

My understanding is that [ -z "$result" ] is true if the string is empty and so then the if statement should return the exit code 1 and the checkhandler should send the message to the Failed folder. I would think that if the mysql call is wrong or if the number does not exist then $result will always be empty and so no messages will be sent so why does the opposite seem to happen?

Again any advice you can give would be good.

Regards

Bill.

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
bill1798 wrote
Does this mean that I have an error in the mysql call?

Yes. I did not notice that until right now, sorry :(.

The SQL statement should be in single argument:

result=`mysql $SQL_ARGS "select number from users where number = \"$to\";"`

After you use those red-colored double-quotation marks, the checkhandler should work as expected.

bill1798 wrote
The checkhandler script is simply an edited version of the script on page 227 of Stephan Fring's manual so I think that it should be more or less correct yet it never seems to return the correct exit code.

Unfortunately the example in the book has a typo.

bill1798 wrote
My understanding is that [ -z "$result" ] is true if the string is empty and so then the if statement should return the exit code 1 and the checkhandler should send the message to the Failed folder. I would think that if the mysql call is wrong or if the number does not exist then $result will always be empty and so no messages will be sent so why does the opposite seem to happen?

Because of the incorrect syntax, mysql returned an "Usage" information and therefore the result was not empty.

Member
Registered:
Jan 2010
Location: Bristol, United Kingdom
Topic owner
Hello Keke,

Sorry for the delay in replying.

The double-quotation marks solved the problem with the SQL call, there was one more typo in the -p command of the sql log on string where there was a space between -p and the password text (-p xxxx instead of -pxxxxx) once this was changed the checkhandler worked like a dream.

I have noticed that there are a lot of tyos in the SMS Tools manual which makes it difficult when you are trying to learn from it, :( but I will continue on and hopefully work things out.

Thanks for your help and the answers to my questions regarding the [ -z $result ] they have been really useful.

Best regards

Bill.

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Good to know that your system is now working well.

If you have any futher issues, feel free to open new topics for them.

bill1798 wrote
I have noticed that there are a lot of tyos in the SMS Tools manual which makes it difficult when you are trying to learn from it, :( but I will continue on and hopefully work things out.

Perhaps you could report those typos to Stefan Frings. I think that it's in his interests to review and fix the book as it's meant to use for learning.

  This topic is locked

Page:  1

SMSTools3 Community » Help and support Top

 
Time in this board is UTC.  

Privacy Policy   SMS Server Tools 3 Copyright © Keijo Kasvi.