Re: Referencing MySQL Results by Column

[halfcountplus]

This isn't at all a perl question, it's an SQL question. You should do a bit of reading about SQL syntax, there's tons of stuff online.

Anyway, for example:

$sqlQuery = "SELECT username,email,phone FROM users WHERE username='bo
+b'";

Always use the single quotes. WRT the perl API, you can also use placeholders like this:

my $sql = $db->prepare("SELECT username,email,phone FROM users WHERE u
+sername=?");
$sql->execute("bob");

The point of using a placeholder is to prevent abuse of the single quote in SQL injection attacks on the database, if say "bob" where actually an $input and you are operating in an insecure environment.

[TheBiscuit]

I apologize for not being clear enough, but this is a Perl question. The script I am trying to modify currently runs a SELECT query with a WHERE statement multiple times within a foreach loop. I would like to run the query once and pull the necessary data from an array instead. Below is the segement of code I'm working with:

# Bind to LDAP Server
$ldap = Net::LDAP->new( $ldapServer ) or die "$@";
$bind = $ldap->bind( $ldapUser, password => $ldapPass );

# Connect to SQL Server
$connect = Mysql->connect($sqlServer, $sqlDB, $sqlUser, $sqlPass);
$connect->selectdb($sqlDB);

$ldapResult     = $ldap->search(
        base => $ldapBaseOU,
        filter => "(&(usertype=Employee))",
        attrs => @ldapAttrs
);

$sqlQuery       = "SELECT username,email,phone FROM users";
$sqlResult      = $connect->query($sqlQuery);

print $sqlResult;

my @entries = $ldapResult->entries;

my $entry;
foreach $entry ( @entries )
{
        my $username = $entry->get_value ( 'sAMAccountName' );
        my $dn = $entry->dn;
        
        ### ***** Insert Match Here ***** ###
        $phone = ??
        $email = ??

}

# Unbind from LDAP Server
$bind = $ldap->unbind;

In the section noted as "***** Insert Match Here *****, I would like to be able to use the $username value pulled from LDAP and match it to the 'username' value pulled from MySQL to set the phone and email variables.

Thanks!

[zwon]

Do not use Mysql, it is obsolete module and its own documentation recommends to use DBI. DBI has selectall_arrayref method which you can use to get all records from the table. If you want to be able to get phone and email by username you can store result into hash like this (not tested):

my $res = $dbh->selectall_arrayref("SELECT username, phone, email FROM
+ users");
my %users = map { $_->[0] => [ $_->[1], $_->[2] ] } @$res;
for (@usernames) {
    my ($phone, $email) = @{$users{$_}};
}

[Krambambuli]

What sort of object is Mysql ?

Maybe you could have what you're after via DBI's selectcol_arrayref.

[afoken]

Always use the single quotes. WRT the perl API, you can also use placeholders

Sorry, bad advise:

1. "Always use single quotes" is too much work. There is no need to quote numbers, doing so makes the job harder for the database.
2. "Always use single quotes" is just plain wrong. It makes people think "just add the magic quotes and everything will be well". Even with the quotes, the SQL is still vulnerable to SQL injection. You really want proper quoting here.
3. While most databases use single quotes, some exotic ones may use other quotes. So, you first need to find out HOW to quote properly.

Luckily, all this has already been done in DBI and the various DBDs, there is no need to reinvent the wheel. So, the last part should really read: "You SHOULD use placeholders". Unfortunately, DBI does not force you to use them, because that would be too much work. It really should, so you better read that advice as "You MUST use placeholders".

Some technical background: DBI guarantees that you can use "?" placeholders with each and every database supported by DBI, even if the database itself does not support placeholders. DBI will automatically insert all bind values, properly quoted, into the SQL statement before it is passed to the database. Since most DBs do support placeholders, using them costs really nothing. Even better, because most DB APIs use seperate ways for the SQL statement and the bind values, there is absolutely no need to quote anything at the Perl or the DB API level. As a nice side effect, your code can work with every database that understands SQL (unless you use some DB-specific, non-standard SQL). And, for extra bonus points, the prepared SQL statement with placeholders can be cached.

Imagine you need to run 20.000 queries (insert, select, update, whatever) against a database, that differ only in the values used. Using hardcoded SQL, you have to generate 20.000 SQL statements, pass them to the database, have the database parse them 20.000 times, and finally execute the one of the 20.000 parsed SQL statements, returning a result. With placeholders, you write ONE query containing placeholders, pass that query ONCE to the database, have the database parse that query ONCE, and finally you play ping-pong with the database: One set of values in, one result out, without any parsing or quoting, as fast as your Perl code can handle the data.

Alexander