package pm::bc_sql; #/ # a module for manipulating an SQLite DB on # Apache in a Windows 10 environment #/ ######################## use strict; use warnings; use Exporter; use vars qw($VERSION @ISA @EXPORT); use CGI::Carp qw(fatalsToBrowser); ######################## use DBI; ######################## $VERSION = 1.00; @ISA = qw(Exporter); @EXPORT = qw( _tests sql_connect sql_create_perchie sql_create_random_user sql_db_user_reset sql_db_valid sql_disconnect sql_execute sql_execute_bound get_about_page get_body_asWord get_cities get_city_id get_city_name get_config get_constant get_constants get_country_cities get_country_id get_country_id_byName get_country_name get_countries get_default_theme get_errors get_eye_clr_asWord get_gender_asWord get_hair_clr_asWord get_height_asWord get_help_page get_home_page get_location get_navbar get_orientation_asWord get_phrase get_profile_views_count get_race_asWord get_security get_security_asWord get_seeking_asWord get_site_name get_theme_data get_themes get_users get_weight_asWord get_zodiacs set_config set_constant is_friend is_admin is_subscriber is_moderator ban_exists ccid_exists fma_exists ipn_exists msg_exists msgs_exist theme_exists user_exists sql_get_user_stat in_maint new_uid new_tid new_ccid read_text validate_new_user_data valid_id valid_ip valid_date valid_config ); ######################## ######################## sub sql_execute($$) { #* # runs an SQL statement on the supplied db. # must be connected, and must sql_disconnect to commit changes. # a reference to an array of hash references can be returned # if only one item is in the array, then we give a hash ref instead # of a one element array reference # (which is a hash reference in and of itself) # three different return values: scalar, array ref, or hash ref # - dependent on # of results, or kind of sql statement (insert, create, update, # select, delete, etc) # !this function does not and will NOT "sanitize" your query - that's your job! # for instance: my $sql = "select * from users where ID = " . $db->quote($uid); # note: this function should only have one return statement at its end...not # several strewn throughout the code like it is here. this will be updated #* my ($db, $sql) = @_; # the dbh && the SQL statement my $prept = $db->prepare($sql); if ($sql =~ /^insert |update |delete /i) { my $rv = $db->do($sql) or die $db->errstr; if ($rv eq undef) { $rv = 0; } if ($rv eq "0E0") { $rv = 0; } # yes. a failure. insert update and delete ought to affect at least one row. return $rv; # 0 on failure, 1 on success } elsif ($prept) { $prept->execute(); # now, grab all the results from the query, and dump them into an array as hash references to each "hit" my @arr = (); while (my $row = $prept->fetchrow_hashref) { push @arr, $row; } if (@arr eq 1 and $arr[0]) { # if the array has only one element, then, it's kinda pointless to give a ref to the array # so instead, let's just give back that hash reference my $hashRef = $arr[0]; # this ought to be a hash reference, no? return $hashRef; # a hash reference when there is only one array element } if (@arr eq 1 and ref $arr[0] ne "HASH") { # if the array has only one element, and it's not a hash reference (meaning $arr[0] =~ /0e0/i) # then ... @arr = (); # clear the array } return \@arr; # an array reference (can be a zero element array), or } else { return \(); # a reference to an empty array when no results, or query failed } #usage: my $rv = sql_execute($db, $sql); } ######################## sub user_exists($$) { #* # determines if a user exists or not #* my ($db, $uid) = @_; # a DBH && a user ID my $sql = "select ID from users where ID = " . $db->quote($uid); my $uref = sql_execute($db, $sql); if (ref $uref eq "HASH") { return 1; # 1 when user exists } else { return 0; # 0 when user does not exist } #usage: if (user_exists($uid)) { print "$uid exists"; } } # ... 1;