proxyserver.html

perl教程

<pre>
        Current statement buffer (enter '/'...):
        alive</pre>
<pre>
        informationdesk@dbi...&gt; /
        COUNT(*)
        '1'
        [1 rows of 1 fields returned]</pre>
<p>
</p>
<h2><a name="testing_the_connection_with_a_perlscript">Testing the connection with a perl-script</a></h2>
<p>Create a perl-script like this:</p>
<pre>
        <span class="comment"># file: oratest.pl</span>
        <span class="comment"># call me like this: perl oratest.pl user password</span>
</pre>
<pre>
        <span class="keyword">use</span> <span class="variable">strict</span><span class="operator">;</span>
        <span class="keyword">use</span> <span class="variable">DBI</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$user</span> <span class="operator">=</span> <span class="keyword">shift</span> <span class="operator">||</span> <span class="keyword">die</span> <span class="string">"Usage: $0 user password"</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$pass</span> <span class="operator">=</span> <span class="keyword">shift</span> <span class="operator">||</span> <span class="keyword">die</span> <span class="string">"Usage: $0 user password"</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$config</span> <span class="operator">=</span> <span class="operator">{</span>
                <span class="string">dsn_at_proxy</span> <span class="operator">=&gt;</span> <span class="string">"dbi:Oracle:e01"</span><span class="operator">,</span>
                <span class="string">proxy</span> <span class="operator">=&gt;</span> <span class="string">"hostname=oechsle.zdf;port=12400"</span><span class="operator">,</span>
        <span class="operator">}</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$dsn</span> <span class="operator">=</span> <span class="keyword">sprintf</span> <span class="string">"dbi:Proxy:%s;dsn=%s"</span><span class="operator">,</span>
                <span class="variable">$config</span><span class="operator">-&gt;</span><span class="operator">{</span><span class="string">proxy</span><span class="operator">}</span><span class="operator">,</span>
                <span class="variable">$config</span><span class="operator">-&gt;</span><span class="operator">{</span><span class="string">dsn_at_proxy</span><span class="operator">}</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$dbh</span> <span class="operator">=</span> <span class="variable">DBI</span><span class="operator">-&gt;</span><span class="keyword">connect</span><span class="operator">(</span> <span class="variable">$dsn</span><span class="operator">,</span> <span class="variable">$user</span><span class="operator">,</span> <span class="variable">$pass</span> <span class="operator">)</span>
                <span class="operator">||</span> <span class="keyword">die</span> <span class="string">"connect did not work: $DBI::errstr"</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"search_city"</span><span class="operator">;</span>
        <span class="keyword">printf</span> <span class="string">"%s\n%s\n%s\n"</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">,</span> <span class="variable">$sql</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">1</span><span class="operator">,</span><span class="string">'905%'</span><span class="operator">);</span>
        <span class="operator">&amp;</span><span class="variable">show_result</span> <span class="operator">(</span><span class="variable">$cur</span><span class="operator">);</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"search_area"</span><span class="operator">;</span>
        <span class="keyword">printf</span> <span class="string">"%s\n%s\n%s\n"</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">,</span> <span class="variable">$sql</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">1</span><span class="operator">,</span><span class="string">'Pfarr%'</span><span class="operator">);</span>
        <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">2</span><span class="operator">,</span><span class="string">'Bronnamberg%'</span><span class="operator">);</span>
        <span class="operator">&amp;</span><span class="variable">show_result</span> <span class="operator">(</span><span class="variable">$cur</span><span class="operator">);</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"statistic_area"</span><span class="operator">;</span>
        <span class="keyword">printf</span> <span class="string">"%s\n%s\n%s\n"</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">,</span> <span class="variable">$sql</span><span class="operator">,</span> <span class="string">"="</span><span class="variable">x40</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">1</span><span class="operator">,</span><span class="string">'Pfarr%'</span><span class="operator">);</span>
        <span class="operator">&amp;</span><span class="variable">show_result</span> <span class="operator">(</span><span class="variable">$cur</span><span class="operator">);</span>
</pre>
<pre>
        <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">disconnect</span><span class="operator">;</span>
        <span class="keyword">exit</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">sub</span><span class="variable"> show_result </span><span class="operator">{</span>
                <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="keyword">shift</span><span class="operator">;</span>
                <span class="keyword">unless</span> <span class="operator">(</span><span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">execute</span><span class="operator">())</span> <span class="operator">{</span>
                        <span class="keyword">print</span> <span class="string">"Could not execute\n"</span><span class="operator">;</span> 
                        <span class="keyword">return</span><span class="operator">;</span> 
                <span class="operator">}</span>
</pre>
<pre>
                <span class="keyword">my</span> <span class="variable">$rownum</span> <span class="operator">=</span> <span class="number">0</span><span class="operator">;</span>
                <span class="keyword">while</span> <span class="operator">(</span><span class="keyword">my</span> <span class="variable">@row</span> <span class="operator">=</span> <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">fetchrow_array</span><span class="operator">())</span> <span class="operator">{</span>
                        <span class="keyword">printf</span> <span class="string">"Row is: %s\n"</span><span class="operator">,</span> <span class="keyword">join</span><span class="operator">(</span><span class="string">", "</span><span class="operator">,</span><span class="variable">@row</span><span class="operator">);</span>
                        <span class="keyword">if</span> <span class="operator">(</span><span class="variable">$rownum</span><span class="operator">++</span> <span class="operator">&gt;</span> <span class="number">5</span><span class="operator">)</span> <span class="operator">{</span>
                                <span class="keyword">print</span> <span class="string">"... and so on\n"</span><span class="operator">;</span>
                                <span class="keyword">last</span><span class="operator">;</span>
                        <span class="operator">}</span>       
                <span class="operator">}</span>
                <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">finish</span><span class="operator">;</span>
                        <span class="operator">}</span>
</pre>
<p>The result</p>
<pre>
        C:\&gt;perl oratest.pl informationdesk xxx
        ========================================
        search_city
        ========================================
        Row is: 3322, 9050, Chemnitz
        Row is: 3678, 9051, Chemnitz
        Row is: 10447, 9051, Chemnitz
        Row is: 12128, 9051, Chemnitz
        Row is: 10954, 90513, Zirndorf
        Row is: 5808, 90513, Zirndorf
        Row is: 5715, 90513, Zirndorf
        ... and so on
        ========================================
        search_area
        ========================================
        Row is: 101, Bronnamberg
        Row is: 400, Pfarramt Zirndorf
        Row is: 400, Pfarramt Rosstal
        Row is: 400, Pfarramt Oberasbach
        Row is: 401, Pfarramt Zirndorf
        Row is: 401, Pfarramt Rosstal
        ========================================
        statistic_area
        ========================================
        DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: Unknown SQL query: statistic_area at E:/Perl/site/lib/DBI/ProxyServer.pm line 258.
        Could not execute</pre>
<p>
</p>
<h2><a name="how_the_configuration_works">How the configuration works</a></h2>
<p>The most important section to control access to your dbi-proxy is &quot;client=&gt;&quot;
in the file &quot;proxy_oracle.cfg&quot;:</p>
<p>Controlling which person at which machine is allowed to access</p>
<ul>
<li><strong><a name="item__22mask_22_is_a_perl_regular_expression_against_th">&quot;mask&quot; is a perl regular expression against the plain ip-address of the machine which wishes to connect _or_ the reverse-lookup from a nameserver.</a></strong>

<li><strong><a name="item_not">&quot;accept&quot; tells the dbiproxy-server wether ip-adresse like in &quot;mask&quot; are allowed to connect or not (0/1)</a></strong>

<li><strong><a name="item__22users_22_is_a_reference_to_a_list_of_usernames_">&quot;users&quot; is a reference to a list of usernames which must be matched, this is NOT a regular expression.</a></strong>

</ul>
<p>Controlling which SQL-statements are allowed</p>
<p>You can put every SQL-statement you like in simply ommiting &quot;sql =&gt; ...&quot;, but the more important thing is to restrict the connection so that only allowed queries are possible.</p>
<p>If you include an sql-section in your config-file like this:</p>
<pre>
        <span class="string">sql</span> <span class="operator">=&gt;</span> <span class="operator">{</span>
                <span class="string">alive</span> <span class="operator">=&gt;</span> <span class="string">'select count(*) from dual'</span><span class="operator">,</span>
                <span class="string">statistic_area</span> <span class="operator">=&gt;</span> <span class="string">'select count(*) from e01admin.e01e203 where geb_bezei like ?'</span><span class="operator">,</span>
        <span class="operator">}</span>
</pre>
<p>The user is allowed to put two queries against the dbi-proxy. The queries are _not_ &quot;select count(*)...&quot;, the queries are &quot;alive&quot; and &quot;statistic_area&quot;! These keywords are replaced by the real query. So you can run a query for &quot;alive&quot;:</p>
<pre>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"alive"</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="operator">...</span>
</pre>
<p>The flexibility is that you can put parameters in the where-part of the query so the query are not static. Simply replace a value in the where-part of the query through a question mark and bind it as a parameter to the query.</p>
<pre>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"statistic_area"</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="variable">$cur</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">1</span><span class="operator">,</span><span class="string">'905%'</span><span class="operator">);</span>
        <span class="comment"># A second parameter would be called like this:</span>
        <span class="comment"># $cur-&gt;bind_param(2,'98%');</span>
</pre>
<p>The result is this query:</p>
<pre>
        select count(*) from e01admin.e01e203 
        where geb_bezei like '905%'</pre>
<p>Don't try to put parameters into the sql-query like this:</p>
<pre>
        <span class="comment"># Does not work like you think.</span>
        <span class="comment"># Only the first word of the query is parsed,</span>
        <span class="comment"># so it's changed to "statistic_area", the rest is omitted.</span>
        <span class="comment"># You _have_ to work with $cur-&gt;bind_param.</span>
        <span class="keyword">my</span> <span class="variable">$sql</span> <span class="operator">=</span> <span class="string">"statistic_area 905%"</span><span class="operator">;</span>
        <span class="keyword">my</span> <span class="variable">$cur</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="variable">$sql</span><span class="operator">);</span>
        <span class="operator">...</span>
</pre>
<p>
</p>
<h2><a name="problems">Problems</a></h2>
<ul>
<li><strong><a name="item_i_don_27t_know_how_to_restrict_users_to_special_da">I don't know how to restrict users to special databases.</a></strong>

<li><strong><a name="item_i_don_27t_know_how_to_pass_query_2dparameters_via_">I don't know how to pass query-parameters via dbish</a></strong>

</ul>
<p>
</p>
<hr />
<h1><a name="author">AUTHOR</a></h1>
<pre>
    Copyright (c) 1997    Jochen Wiedmann
                          Am Eisteich 9
                          72555 Metzingen
                          Germany</pre>
<pre>
                          Email: [e-mail]
                          Phone: [phone]</pre>
<p>The DBI::ProxyServer module is free software; you can redistribute it
and/or modify it under the same terms as Perl itself. In particular
permission is granted to Tim Bunce for distributing this as a part of
the DBI.</p>
<p>
</p>
<hr />
<h1><a name="see_also">SEE ALSO</a></h1>
<p><a href="../../bin/dbiproxy.html">the dbiproxy manpage</a>, <a href="../../lib/DBD/Proxy.html">the DBD::Proxy manpage</a>, <a href="../../lib/DBI.html">the DBI manpage</a>, <a href="../../RPC/PlServer.html">the RPC::PlServer manpage</a>,
<a href="../../RPC/PlClient.html">the RPC::PlClient manpage</a>, <a href="../../Net/Daemon.html">the Net::Daemon manpage</a>, <a href="../../Net/Daemon/Log.html">the Net::Daemon::Log manpage</a>,
<a href="../../Sys/Syslog.html">the Sys::Syslog manpage</a>, <a href="../../lib/Win32/EventLog.html">the Win32::EventLog manpage</a>, <em>syslog</em></p>

</body>

</html>