SQL-based authentication: Difference between revisions

This is the table definition:

userid int primary key auto_increment,

username varchar(32) unique key not null,

pass_md5 tinyblob not null

-- binary MD5 hash of pass_salt concatenated with the password

(<tt>pass_salt</tt> and <tt>pass_md5</tt> would be <tt>binary(16)</tt> values, but MySQL versions before 5.0.15 strip trailing spaces when selecting them.)

{{libheader|mysqlclient}} (MySQL client library)

{{libheader|OpenSSL}} (for MD5)

#include <stdlib.h>

#include <string.h>

}

return EXIT_SUCCESS;

From the command line, <tt>program add user password</tt> to add users, and <tt>program auth user password</tt> to see if the user with that password is authorized or not.

=={{header|C sharp|C#}}==

Class for hashing and random salt generation.

using System.Text;

}

}

Class for creating and authenticating users.

namespace rosettaMySQL

}

}

Class with main method and database connection method.

using MySql.Data.MySqlClient;

}

}

Output

<pre>Bob has been created: True

=={{header|Go}}==

import (

// clear table to run program again

db.Exec(`truncate table users`)

{{out}}

<pre>

=={{header|Java}}==

{{works with|Java|1.7}}

import java.sql.Connection;

import java.sql.DriverManager;

}

}

=={{header|Julia}}==

using MySQL

using Nettle # for md5

println("""Mary does not authenticate with password of 123: $(authenticate_user(mydb, "Mary", "123")==false)""")

mysql_disconnect(mydb)

=={{header|Kotlin}}==

{{trans|Java}}

import java.sql.Connection

}

}

=={{header|Mathematica}}/{{header|Wolfram Language}}==

{{libheader|DatabaseLink`}}

connectDb[dbUser_, dbPass_, dbUrl_] :=

OpenSQLConnection[JDBC["mysql", dbUrl], "Username" -> dbUser,

SQLColumn["username"] == user][[1]]},

Hash[FromCharacterCode[data[[2, 1]]] <> pass, "MD5"] ==

=={{header|Nim}}==

Even if this program is only an example, we have chosen to generate the salt using the random number generator provided by the third-party module “nimcrypto” rather than using the PRNG from the standard module “random” which is totally inadequate for cryptographic usage.

proc connectDb(user, password: string): DbConn =

# Clean-up and close.

db.clean()

{{out}}

=={{header|Objeck}}==

use Encryption;

return true;

}

=={{header|Perl}}==

{{libheader|DBI}}<br>

{{libheader|DBD::mysql}}

# returns a database handle configured to throw an exception on query errors

undef, $user, $pass);

$userid

=={{header|Phix}}==

Uses SQLite, there is a MySQL wrapper in [http://phix.x10.mx/pmwiki/pmwiki.php PCAN] that I have not personally tried yet.<br>

Uses code from [[MD5/Implementation#Phix|MD5]], now modified to be an executable library.

<span style="color: #000080;font-style:italic;">-- demo\rosetta\SQL-based_authentication.exw</span>

<span style="color: #008080;">without</span> <span style="color: #008080;">js</span> <span style="color: #000080;font-style:italic;">-- (file i/o)</span>

<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>

<span style="color: #000000;">main</span><span style="color: #0000FF;">()</span>

{{out}}

<pre>

=={{header|PHP}}==

To use MySQL in PHP you need the php_mysql module installed

function connect_db($database, $db_user, $db_password, $host = 'localhost', $port = NULL, $die = false) {

// Returns a MySQL link identifier (handle) on success

return $row['userid'];

}

=={{header|Python}}==

{{works with|Python|2.7}}

Uses the [http://dev.mysql.com/downloads/connector/python/ official Python MySQL connector]

import hashlib

else:

print 'User %s failed' % user

=={{header|Racket}}==

(require db file/md5)

(define-logger authentication)

(check-false (create-user test-DB #"tim" #"tim's password"))

(check-exn exn:fail? (λ () (authenticate-user test-DB #"tim" #"password")))

=={{header|Raku}}==

(formerly Perl 6)

{{trans|Perl}}

use v6;

use DBIish;

my $userid = $sth.fetch;

$userid[0] or Any;

=={{header|Raven}}==

MySQL connectivity is available out of the box. Below, we do not ever send the plain text password over the wire.

'abcdefghijklmnopqrstuvwxyz0123456789' as $salt_chars

'foo' 'bar' authenticate_user !if "could not authenticate user\n" print bye

=={{header|Ruby}}==

Uses the [https://github.com/brianmario/mysql2 mysql2 gem]

require 'securerandom'

require 'digest'

password_md5 = Digest::MD5.hexdigest(user_record['pass_salt'] + password)

password_md5 == user_record['pass_md5']

=={{header|Sidef}}==

{{trans|Perl}}

# returns a database handle configured to throw an exception on query errors

nil, user, pass

)

=={{header|Tcl}}==

{{works with|Tcl|8.6}}

Also requires the TDBC driver for MySQL.

proc connect_db {handleName dbname host user pass} {

# Only get here if no rows selected

error "authentication failed for user \"$user\""

=={{header|Wren}}==

Not an exact 'phiximile' but follows the same lines.

import "./crypto" for Md5

addUser.call(db, "user", "password")

System.print("User with correct password: %(authenticateUser.call(db, "user", "password"))")

{{out}}