Reading a MySQL Field in the dialplan

Asterisk Asterisk Dialplan
1

Hello everyone,
I need to read a value contained in a MySQL table into the dialplan.
I saw that I can use MySQL or ODBC.
I find a lot of tutorials for ODBC but only to insert values (cdr) in the database.
Is the use of MySQL still possible knowing that it is no longer possible to install the module (Asterisk 16.21.1)?
If I have to use ODBC, have you documentation about reading a value from a table?
thanks in advance

2

Hello,

You can use readsql. Here is a reference:

3

Most people love ODBC I prefer to use shell() function for reading or writing on MySQL

Something like this

same=>n,Set(trunk1=${SHELL(mysql --user=root --password='456' --skip-column-names asterisk -e "select client_trunk_group_id from dids where did ="${EXTEN}" order by priority asc limit 0,1 ")})

4

I finally got it via the shell. I would love to read it via googleTTS but it seems that there is a special character or something :

There is my context :

exten => 803,1,Set(credit=${SHELL(mysql --user=xxxxxx --password='xxxxxx' --skip-column-names dbname -e"select credit from user where numposte=${CHANNEL(endpoint):2}")})
same = n,agi(googletts.agi,"Votre crédit est : ${credit}",fr,any,1.5)
same = n,Hangup()

There is the log :

-- Executing [803@Streamer:1] Set("PJSIP/1067-00001a21", "credit=16.7462
    -- ") in new stack
    -- Executing [803@Streamer:2] AGI("PJSIP/1067-00001a21", "googletts.agi,"Votre crédit est : 16.7462
    -- ",fr,any,1.5") in new stack

So the query is working but there is a line break on the value.
How can I delete it ?
Thanks!

5

In this case, I think ${credit:-1} should be enough.

6

Thanks!
Yes, it works!
Thanks again, odbc was very hard to understand to me

7

Another approach would be to pipe the output from ‘mysql’ through ‘tr’

Trimming the channel variable is (nanoseconds) faster, tr is more obvious (to the next guy – even if it happens to be you in x years), featureful, and robust.

You may want to reconsider putting the password on the command line. If anybody accesses extensions.conf or convinces Asterisk to dump your dialplan (remotely via AMI?) it is visible in plaintext.

Also, if somebody does a ‘ps’ while the command is executing, you are leaking information. MySQL does overwrite the password with ‘x xxx’ (yes, x, space, more x es) but it keeps the original password length which may be of significance to an attacker.

In descending order of security:

  1. Use --login-path.
  2. Use .my.cnf
  3. Use credentials on the command line.

If I were to write this command, I would do it like:

set(credit=${SHELL(mysql --login-path=example --disable-column-names --disable-table --execute='select ...\;' | tr -d '\n')})
or
set(credit=${SHELL(mysql --database=example --disable-column-names --disable-table --execute='select ...\;' --host=example --password=example --user=example | tr -d '\n')})

because I’m a verbose and ‘keep it in alphabetic order’ kind of guy.

Note that ‘login-path’ likes to be the first option and if you include the semicolon in the SQL, you need to quote it.

8

Or maybe as an AGI, which is overkill to retrieve a single datum from the database, but once you give 'em an inch, they’ll be back for a mile :slight_smile:

9

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.