MySQL queries via Telnet?

[chadrt]

Ok I will try to explain myself here without loosing someone.  I have a MySQL server running and have built a very large database.  I would like be able to run queries on this database.  But here is the catch.  I want to be able to telnet into a script that will run the queries.  Example process:

 

1. In windows I run "cmd"

2. At prompt I run telnet db.myhost.com 2111

3. My server responds with special prompt that is asking for the search term.  (no logins, no passwords, just an immediate front end to the database itself.)

4. I enter search term it executes SELECT * FROM mytable WHERE term='TermEntered'

 

You get the idea here I may be in the wrong forum all together but I thought MySQL server that I run it on then maybe someone could at least point me in the right direction?  Thank you for any help...

[Wildbug]

You can run queries remotely using the mysql client; you just need to set up a MySQL user who can log on from a remote source.  You can even run premade queries this way.

[Wildbug]

If you really need to have something listening on a port for a single query, you could write a Perl script to do so easily enough.  You'll need to know about sockets, daemonization, and DBD/DBI+MySQL to do so.  You can have the program put itself in the background, listen on a port, and perform a prepared query with the string you send it.  I don't know how much of a security hole this would be, but using prepared statements or even stored functions would cut down on insecurity.  I still think mysql client is a better solution, though.

[Wildbug]

Because I have better things to be doing but will take any excuse not to do them, and despite the fact that I think you should be doing this via mysql client, but I kinda wanted to see if I could do it, I cobbled together a quick Perl script that does what you originally asked for.  It may need some tweaking, but it'll give you the idea.

 

Database, user, password, query, and prompt (what you see when you connect) get set at the top of the script.  When you connect it will look like this:

 

Example:

telnet localhost 2111
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
Connected!
query> test
  001 this is a test
  003 more test text
query> 

 

script:

#!/usr/bin/perl -w
# ./querysock - a perl script for connect to a database via telnet to execute a predefined query
# References:
# http://www.devshed.com/c/a/Perl/Socket-Programming-in-PERL/3/
# Linux Daemon Writing HOWTO

use strict;
use IO::Socket;
use POSIX;
use DBI;

my ($db,$dbuser,$dbpass) = ('test','test','test');
my $prompt = 'query>';
my $query_string = q{ SELECT * FROM test1 WHERE text RLIKE ? };

# DAEMONIZE PROCESS -----------------------------------------------------------

# Flush output buffer
$| = 1;

# Detach from user process
defined (my $pid = fork) or die "Can't start daemon: $!";
sleep 1 and exit if $pid;
umask 0;
setsid or die "Can't start a new session: $!";

# Chdir to root (to avoid tying up mounted volumes)
chdir '/' or die "Can't chdir to /: $!";

# SET UP SOCKET ---------------------------------------------------------------

my $port = shift || 2111;
my $sock = new IO::Socket::INET(
        LocalHost       => 'localhost',
        LocalPort       => $port,
        Proto           => 'tcp',
        Listen          => SOMAXCONN,
        Reuse           => 1
) or die "No socket: $!\n";

# CONNECT TO DATABASE ---------------------------------------------------------

my $database = DBI->connect(
        "DBI:mysql:database=$db;host=localhost",
        $dbuser,
        $dbpass
) or die "Failed to connect to database (${DBI::errstr}).\n";

my $query = $database->prepare($query_string);

# MAIN LOOP -------------------------------------------------------------------

# Reroute or close standard I/O; you can redirect to a logfile, btw.
close STDIN; close STDOUT; close STDERR;

my ($newsock,$c_addr,$buf,@row);
while (($newsock,$c_addr) = $sock->accept()) {
        my ($client_port,$c_ip) = sockaddr_in($c_addr);
        my $client_ipnum = inet_ntoa($c_ip);
        my $client_host = gethostbyaddr($c_ip, AF_INET);
        print $newsock "Connected!\n$prompt ";

        while (defined ($buf = <$newsock>)) {
                $buf =~ s/[^\w\d]//g; # Strip any non alphanumeric characters.
                if ($query->execute($buf)) {;
                        while (@row = $query->fetchrow_array()) {
                                print $newsock "  @row\n";
                        }
                        $query->finish;
                }
                print $newsock "$prompt ";
        }
} 

exit;

 

But this could be better done by doing (in *nix; I forget how batch files look, but it can be easily converted to a Windows script):

 

#!/bin/sh
if [ $1 != "" ]; then
mysql --host=wherever.host.com --user=username -p -e "SELECT * FROM table WHERE column='$1'"
else
echo You forgot to enter a search term.
fi

[chadrt]

Wildbug,

Thank you, my purpose was not to have full control over or even a partial control over the mysql prog.  I built a database with over a million records from the FCC, the database updates itself every day, and gives its users the ability to lookup other Amateur Radio Operators.  I got some advice and help with a script, that loads the db, from someone who worked on one for the university of alabama on this database and while I was looking at their site I noticed they have a telnet interface to the call sign script. I thought it was interesting and I just started to wonder what it would take to do such a thing.

 

telnet callsign.ualr.edu 2000

 

It is a super simple interface and I couldnt find the address to the site till now.  It would be a public interface for anyone to use.  I went looking before my last post but was so tired this morning that it just slipped my mind.  (I pay for a Virtual Private Server on a SUSE LINUX platform just for the purpose of my database and for learning Linux. So wouldnt need to run it on Windows as I have root on this VPS.)  You can use my callsign KI4MVE to search with in that database if you look at it.

 

Thank you for making that script.  It will get me moving in the right direction anyway.  Like anything else I have taken on I will have to teach myself some of the perl scripting language I suppose to do such a thing.  And I dont know what I was thinking this is not a simple database anyway it is a relational database I have to display information from three tables.  So I suppose I have a lot of work to do.  But this will get me going, thank you!!

[Wildbug]

Also, you could write a simple PHP script with a $_GET variable as input to a single SQL query....

 

Thank you for making that script.  It will get me moving in the right direction anyway.  Like anything else I have taken on I will have to teach myself some of the perl scripting language I suppose to do such a thing.  And I dont know what I was thinking this is not a simple database anyway it is a relational database I have to display information from three tables.  So I suppose I have a lot of work to do.  But this will get me going, thank you!!

 

No problem...  If you really want to have this over telnet, I could brush up the script a bit more.  The idea intrigued me, so I wrote it quickly just to see how to do it.  I hesitated to claim it was production-ready because (a) I didn't want to put alot of work into it if it wasn't going to be used, (b) I haven't really audited it out in terms of security or robustness, and © it's not that well documented or commented.  I think that it could be better written, maybe using child processes to handle multiple connections, some event handlers to exit more gracefully, error logging, etc.  Let me know.

[chadrt]

Wildbug,

 

In regards to the PHP script I have written all my scripts so far in PHP with a lot of help from these forums.  I had thought of doing something like already but how to get the input into the script and back to telnet?  My query looks like this in PHP,

 

<?
$username="cs_ro_dev"; // CallSign_ReadOnly_Development User
$password="*********"; // Read Only Password
$database="uls";
$dbhost="callsigndb.info";

$norequest="Nothing was entered for me to search with!";
$noresult="Callsign Not Found";
$cs=$_REQUEST['cs'];

mysql_connect($dbhost,$username,$password);
@mysql_select_db($database) or die( "Unable to connect to database please try again!");

# This makes sure that there is something to search for or returns an error.
if ($cs=='') {
echo $norequest;
} else {
$search="callsign='$cs' AND license_status='A'";



$queryhd1="SELECT * FROM HD WHERE $search";
$resulthd1=mysql_query($queryhd1);

# This counts the number of returned results from the database search
$num=mysql_numrows($resulthd1);

# This says if that number = 0 (nothing) then display the $noresult variable
if ($num==0) {
echo $noresult;
}else {

############################################################################
$uniqueresult=mysql_result($resulthd1,$i,"unique_id");


$queryam="SELECT * FROM AM WHERE unique_id='$uniqueresult'";
$resultam=mysql_query($queryam);

$queryen="SELECT * FROM EN WHERE unique_id='$uniqueresult'";
$resulten=mysql_query($queryen);

$queryhd="SELECT * FROM HD WHERE unique_id='$uniqueresult'";
$resulthd=mysql_query($queryhd);
mysql_close();

# Entries from table EN below
$entity_name=mysql_result($resulten,$i,"entity_name");
$street_address=mysql_result($resulten,$i,"street_address");
$city=mysql_result($resulten,$i,"city");
$state=mysql_result($resulten,$i,"state");
$zipcode=mysql_result($resulten,$i,"zipcode");
$po_box=mysql_result($resulten,$i,"po_box");

# Entries from table AM below
$callsign=mysql_result($resultam,$i,"callsign");
$operator_class=mysql_result($resultam,$i,"operator_class");
$group_code=mysql_result($resultam,$i,"group_code");
$previous_callsign=mysql_result($resultam,$i,"previous_callsign");
$previous_operator_class=mysql_result($resultam,$i,"previous_operator_class");
$unique_id=mysql_result($resultam,$i,"unique_id");

# Entries from table HD below
$license_status=mysql_result($resulthd,$i,"license_status");
$callsign=mysql_result($resulthd,$i,"callsign");
$grant_date=mysql_result($resulthd,$i,"grant_date");
$expired_date=mysql_result($resulthd,$i,"expired_date");

# The next three sections take what is in the database in and changes it so that it is understood more eaily
############################################################################################################
# String Replacements (added IF statements due to php getting confused very perplexing actually!)
if ($license_status=='A'){
$status = str_replace('A','<font color=green>ACTIVE</font>', $license_status);}
if ($license_status=='C'){
$status = str_replace('C','<font color=red>Canceled</font>', $license_status);}
if ($license_status=='E'){
$status = str_replace('E','<font color=red>Expired</font>', $license_status);}

# What is even weirder is that there is no confusion here for the operator clase replacement strings!
$operator_class = str_replace('A','Advanced', $operator_class);
$operator_class = str_replace('E','Extra', $operator_class);
$operator_class = str_replace('G','General', $operator_class);
$operator_class = str_replace('T','Technician', $operator_class);
$operator_class = str_replace('N','Novice', $operator_class);

# String replacements for Operator Class field in the database.
$previous_operator_class = str_replace('A','Advanced', $previous_operator_class);
$previous_operator_class = str_replace('E','Extra', $previous_operator_class);
$previous_operator_class = str_replace('G','General', $previous_operator_class);
$previous_operator_class = str_replace('T','Technician', $previous_operator_class);
$previous_operator_class = str_replace('N','Novice', $previous_operator_class);

# Prevents errors if nothing is found.
if ($num==0) {
} else {

# PO Box Display output the way this works is is checks to see if the $po_box value is empty or not
# if the value is empty then it makes $pobox=nothing that way nothing is displayed when $pobox is called upon below
# in the Display output.
if ($po_box=='') {
$pobox="";
} else {
$pobox="PO Box $po_box";
}

# Displays the search output on the screen!
echo "<b>$callsign</b> is an <b>$status</b> License<br><br>";
echo "This license has been issued to:<br>";
echo "$entity_name<br>";
echo "$street_address$pobox<br>$city, $state $zipcode<br><br>";
echo "Operator License Class: $operator_class<br>";
echo "License Issued from Group: $group_code<br>";
echo "Previous Call Sign: $previous_callsign<br>";
echo "Previous Operator Class: $previous_operator_class<br>";
echo "Date License Issued: $grant_date<br>";
echo "Date License Expires: $expired_date<br>";

echo "<br><a href=http://wireless2.fcc.gov/UlsApp/UlsSearch/license.jsp?licKey=$unique_id>View FCC record for $callsign</a>";
  }
}
}

 

I could probably use that script above I have started working on it now so it will work in a text environment but I dont know how to pass varriables to it so it will work.  I have the PHP working but all I get is my standard error message when I run it because it is not seeing my query term.  I would be most appreciative if did refine the code even it only passed the information to a PHP script that I can build and take the output from it back to the daemon.

 

Chad

[chadrt]

Ok I was able to modify the script and make it just right I can call it using ./call.php callsign and it is even formatted correctly too.  So I think that is what you were talking about.  I am glad to see that works good in the shell environment.  If you need the script that I made let me know.  All I made was the CALL search.  The other things that the university telnet app is capable of is pretty nice and advanced but I am sure it is a lot of work to do that, especially for a hobby site like mine.  I am now trying to find a way to open the port 2111.  So I can get in to the script once it is there.  Using what you already posted I am able to login via telnet on localhost using an SSH session.  We'll see how it goes.

 

Chad

[chadrt]

Wildbug,

Just wondered if you had come up with anything.  you had said you didnt want to work on something that wouldnt get used, believe me I understand that but I will use it.  I did make that PHP script that works in a shell environment that will ask for the input, run the query and return the data.  If that would be usefull I can zip that up for ya.  If however you cannot spare the time then I definately understand that too.  Just know that I appreciate the code you have already prepared for me.  Thank you...

[Wildbug]

Hey chadrt,

 

I did work a little on it, creating a version which spawns child processes to handle each connection, so you can have multiple users at a time.  I haven't had a chance to work on it since this weekend, though.

 

If you want multiple commands, that could be accomplished easily enough by making a hash containing possible commands.

 

# For example:
%commands = (
'callsign' => $database->prepare("SELECT * FROM database WHERE callsign = ?"),
'location' => $database->prepare("SELECT * FROM database WHERE location = ?")
);

# for each line of input:
/^\s*(\w+)(.*)$/;  # get the first word
$commands{$1}->execute($2);  # and use it to select the query

# etc...

(I skipped error checking, but obviously that, too.)

 

I'll work on it more this week.

 

The PHP solution I was suggesting was web-based.

 

<?php

if ($_GET['q']) {
mysql_connect('localhost','user','pass');
mysql_select_db('db');
$result = mysql_query('SELECT * FROM table WHERE callsign="'.mysql_real_escape_string($_GET['q']) . '"';
if ($result && mysql_num_rows($result)) {
while ($row = mysql_fetch_row($result)) echo implode(' ',$row),"<br />\n";
}

?>

[chadrt]

When you said PHP I assumed you were talking of using PHP to access the data for perl.    I had made a PHP script that operated in a shell environment so that the pl script could send the data to it and then have it process the query and return it in an all text format.

 

My web site www.callsigndb.info is a site where I have all my stuff located for that project.  I am even incorporating the call sign look up into the normal profile system that PHP-Nuke has in place.  I added fields in the users table for all the added info and then query'd it as well and if the person sets the callsign in their profile information settings then when you look up their profile it shows their call sign lookup too.  I am trying now to make it the other way as well but that is proving to be a bit more chalenging for a beginner like me.  But when I started that project I knew nothing about databases or PHP or otherwise and that was about 6-8 weeks ago.  I am getting there slowly but surely.  (Next to tackle another language but not sure which one: Java, Perl, ASP, etc. have to really look into them!)

 

Thank you so much for your work, you wouldnt happen to have an address I could send you something for your time?  I am not a rich person but I'd sure like to at least buy you a nice steak diner or something for you and yours!  Just send me a PM or something.

 

 

Chad